#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : service_centre_day_report.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-05-13 18:49 
@Desc    : 新版服务中心日报
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import pandas as pd
from report_system.utils import db_util
from report_system.utils import date_util
from report_system.utils import df_util
from report_system.utils.log_util import log
from report_system.utils.num_util import percentage


def phone(st, et):
    # sql = """
    #     select
    #     uo.name 门店,
    #     uoa.area_id 城市id,
    #     city.short_name 城市,
    #     aacr.bind_mobile 绑定电话,
    #     group_concat(distinct users.realname) 门店销售,
    #     aacr.saler_mobile 转拨电话,
    #     users.mobile 销售绑定电话,
    #     count(distinct aacr.id) 来电次数,
    #     if(aacr.time_total > 0,1,0)  接起电话次数,
    #     count(distinct aacr.customer_mobile)  来电人次,
    #     count(distinct if(aacr.time_total > 0,aacr.customer_mobile,null) ) 接起电话人次
    #     FROM youjia_common.ali_axn_call_record aacr
    #     LEFT JOIN youjia.user_org uo ON uo.phone_num = aacr.bind_mobile and uo.is_delete = 0
    #     left JOIN youjia.user_org_role role ON role.org_id=uo.id and role.is_delete = 0 AND role.role_id = 5
    #     left JOIN youjia.users ON role.user_id=users.id
    #     LEFT JOIN youjia.user_org uo1 ON uo1.id = uo.parent_id
    #     LEFT JOIN youjia.user_org_area uoa on uoa.org_id = uo1.id and uoa.type = 1
    #     LEFT JOIN youjia.city on city.id = uoa.area_id
    #     WHERE DATE(aacr.call_time) BETWEEN '{st}' AND '{et}'
    #     AND HOUR(aacr.call_time) BETWEEN '09' AND '20'
    #     and (TIMESTAMPDIFF(SECOND,aacr.ring_time, aacr.start_time ) > 15
    #         or (TIMESTAMPDIFF(SECOND,aacr.ring_time, aacr.start_time ) <= 15 and aacr.time_total > 0 ))
    #     AND aacr.call_type = 1
    #     and aacr.start_time is not null
    #     and uo.name IS NOT NULL
    #     AND aacr.bind_mobile <> aacr.saler_mobile
    #     GROUP BY aacr.id
    #     """.format(st=st, et=et)
    sql = """select    
            aacr.bind_mobile 绑定电话,
            aacr.saler_mobile 转拨电话,
            count(distinct aacr.id) 来电次数,
            if(aacr.time_total > 0,1,0)  接起电话次数,
            count(distinct aacr.customer_mobile)  来电人次,
            count(distinct if(aacr.time_total > 0,aacr.customer_mobile,null) ) 接起电话人次 
            FROM youjia_common.ali_axn_call_record aacr
            WHERE DATE(aacr.call_time) BETWEEN '{st}' AND '{et}'
            AND HOUR(aacr.call_time) BETWEEN '09' AND '20'
            and (TIMESTAMPDIFF(SECOND,aacr.ring_time, aacr.start_time ) > 15 
              or (TIMESTAMPDIFF(SECOND,aacr.ring_time, aacr.start_time ) <= 15 and aacr.time_total > 0 ))
            AND aacr.call_type = 1 
            and aacr.start_time is not null
            AND aacr.bind_mobile <> aacr.saler_mobile
            GROUP BY aacr.id""".format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def phone_detail(st, et):
    """
    :param st:
    :param et:
    :return:
    """
    # sql = """
    #     select
    #     uo.name 门店,
    #     city.short_name 城市,
    #     aacr.bind_mobile 阿里绑定电话,
    #     users.realname 门店销售,
    #     aacr.saler_mobile 转拨电话,
    #     users.mobile 销售绑定电话 ,
    #     aacr.call_time 呼叫动作开始时间,
    #     aacr.ring_time 手机振铃时间,
    #     aacr.start_time 接听时间,
    #     aacr.release_time 挂断时间,
    #     aacr.time_total 通话时长,
    #     aacr.create_time 通话记录创建时间
    #     FROM youjia_common.ali_axn_call_record aacr
    #     LEFT JOIN youjia.user_org uo ON uo.phone_num = aacr.bind_mobile and uo.is_delete = 0
    #     left JOIN youjia.user_org_role role ON role.org_id=uo.id and role.is_delete = 0 AND role.role_id = 5
    #     left JOIN youjia.users ON role.user_id=users.id
    #     LEFT JOIN youjia.user_org uo1 ON uo1.id = uo.parent_id
    #     LEFT JOIN youjia.user_org_area uoa on uoa.org_id = uo1.id and uoa.type = 1
    #     LEFT JOIN youjia.city on city.id = uoa.area_id
    #     WHERE DATE(aacr.call_time) BETWEEN '{st}' AND '{et}'
    #     AND HOUR(aacr.call_time) BETWEEN '09' AND '20'
    #     AND aacr.call_type = 1
    #     and aacr.start_time is not null
    #     and uo.name IS NOT NULL
    #     and (TIMESTAMPDIFF(SECOND,aacr.ring_time, aacr.start_time ) > 15
    #         or (TIMESTAMPDIFF(SECOND,aacr.ring_time, aacr.start_time ) <= 15 and aacr.time_total > 0 ))
    #     and users.realname is not null
    #     AND aacr.bind_mobile <> aacr.saler_mobile
    #     GROUP BY aacr.id
    #     order by users.mobile,aacr.call_time
    #      """.format(st=st, et=et)
    sql = """
        select        
        aacr.bind_mobile 绑定电话,
        aacr.saler_mobile 转拨电话, 
        aacr.call_time 呼叫动作开始时间,
        aacr.ring_time 手机振铃时间,
        aacr.start_time 接听时间,
        aacr.release_time 挂断时间,
        aacr.time_total 通话时长,
        aacr.create_time 通话记录创建时间
        FROM youjia_common.ali_axn_call_record aacr
        WHERE DATE(aacr.call_time) BETWEEN '{st}' AND '{et}'
        AND HOUR(aacr.call_time) BETWEEN '09' AND '20'
        AND aacr.call_type = 1
        and aacr.start_time is not null
        and (TIMESTAMPDIFF(SECOND,aacr.ring_time, aacr.start_time ) > 15 
            or (TIMESTAMPDIFF(SECOND,aacr.ring_time, aacr.start_time ) <= 15 and aacr.time_total > 0 ))
        AND aacr.bind_mobile <> aacr.saler_mobile
        GROUP BY aacr.id
        order by  aacr.bind_mobile,aacr.saler_mobile,aacr.call_time
         """.format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def im(st, et, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    im 详情
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:
    """
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    if isinstance(channel_ids, str) or isinstance(channel_ids, int):
        channel_ids = '({channel_ids})'.format(channel_ids=channel_ids)
    if isinstance(channel_ids, list):
        if channel_ids.__len__() == 1:
            channel_ids = '({channel_ids})'.format(channel_ids=channel_ids[0])
        else:
            channel_ids = tuple(channel_ids)

    sql = """select 
    if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct A.大区)) 大区,
    if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct A.work_name)) 门店,
    if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct A.realname order by A.realname)) 门店销售,
    if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct A.门店管家 order by A.门店管家)) 门店管家,
    if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct ifnull(A.服务中心,'服务中心为空'))) 服务中心,
    if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct concat(A.lodge_id,"")))  房源id,
    if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct concat(A.house_id,"")))  房屋ID,
    if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct A.short_name)) 城市,
     case group_concat(distinct A.own_type order by A.own_type)
     when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
     if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct A.name)) 渠道,
     if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct A.channel_id)) channel_id,
    sum(A.咨询数)  咨询数,
    sum(A.回复数) 回复数,
    sum(if(timestampdiff(second, A.最早咨询时间 ,A.最早回复时间) <= 5*60,A.回复数,0)) 五分钟回复数,
    sum(if(A.最晚回复时间>=A.最晚咨询时间 ,A.回复数,0)) 完整回复数
    from(
            select
            it.id it_id,
            uo.name work_name,
            uosc.sname 服务中心,
            uo3.name  大区,
            lh.own_type,
            u1.realname,
            u2.realname 门店管家,
            uo.id work_id,
            it.lodge_id,
            lh.id house_id,
            lh.city_id,
            c.short_name,
            sc.name,
            ims.channel_id ,
            count(distinct if(ims.from_user_type = 1,it.id,null)) 咨询数,
            count(distinct if(ims.from_user_type = 2,it.id,null)) 回复数,
            min(if(ims.from_user_type = 1,ims.create_time,null)) 最早咨询时间,
            min(if(ims.from_user_type = 2,ims.create_time,null)) 最早回复时间,
            max(if(ims.from_user_type = 1,ims.create_time,null)) 最晚咨询时间,
            max(if(ims.from_user_type = 2,ims.create_time,null)) 最晚回复时间
            FROM youjia_im.im_talks it
            LEFT JOIN youjia_im.im_msgs ims ON ims.talk_id = it.id
            left join youjia_common.sales_channel sc on sc.id = ims.channel_id 
            left join youjia.lod_house lh on lh.lodge_id = it.lodge_id
            left join youjia.city c on c.id = it.city_id
            left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
            left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
            LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
            left join youjia.user_org uo on uo.id = lh.work_id
            left join youjia.user_org uo2 on uo2.id=uo.parent_id and uo2.is_delete = 0
		    left join youjia.user_org uo3 on uo3.id=uo2.parent_id and uo3.is_delete = 0
            left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id
            where it.channel_id in {channel_ids}
            and lh.own_type in {own_type}
            and it.work_name NOT LIKE '%%五家渠%%'
            and lh.city_id <> 376
            and DATE(it.create_time) BETWEEN '{st}' AND '{et}'
            AND HOUR(it.create_time) BETWEEN '9' AND '20'
            group by it.id) A
    group by A.own_type , A.it_id
    with rollup;""".format(st=st, et=et, own_type=own_type, channel_ids=channel_ids)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def im_conversion_order(st, et, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    im转化详情
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:
    """
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    if isinstance(channel_ids, str) or isinstance(channel_ids, int):
        channel_ids = '({channel_ids})'.format(channel_ids=channel_ids)
    if isinstance(channel_ids, list):
        if channel_ids.__len__() == 1:
            channel_ids = '({channel_ids})'.format(channel_ids=channel_ids[0])
        else:
            channel_ids = tuple(channel_ids)
    sql = """select 
            if(COALESCE(it.id,'全国')='全国','全国', group_concat(distinct uo3.name) )  大区,
            if(COALESCE(it.id,'全国')='全国','全国', group_concat(distinct city.short_name) )  城市,
             case group_concat(distinct lh.own_type order by lh.own_type)
             when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            if(COALESCE(it.id,'全国')='全国','全国',group_concat(distinct concat(it.id,"") )) 对话id,
            if(COALESCE(it.id,'全国')='全国','全国',group_concat(distinct concat(it.lodge_id,""))) 房源id,
            if(COALESCE(it.id,'全国')='全国','全国',group_concat(distinct concat(lh.id,""))) 房屋ID,
            ifnull(if(COALESCE(it.id,'全国')='全国','全国',group_concat(distinct uosc.sname order by uosc.sname)),'服务中心为空') 服务中心,
            if(COALESCE(it.id,'全国')='全国','全国',group_concat(distinct uo.name order by uo.name)) 门店,
            if(COALESCE(it.id,'全国')='全国','全国',group_concat(distinct sc.name order by sc.name)) 渠道,
            if(COALESCE(it.id,'全国')='全国','全国',group_concat(distinct u1.realname order by u1.realname)) 门店销售,
            if(COALESCE(it.id,'全国')='全国','全国',group_concat(distinct u2.realname order by u1.realname)) 门店管家,
            count(distinct it.id) 咨询数,
            count(distinct bo.order_id) 下单数
            from youjia_im.im_talks it
            left join (SELECT   bo.id order_id,
                                u.id user_id,
                                bo.order_channel,
                                bo.lodgeunit_id lodge_id
                FROM youjia.orders bo
                LEFT JOIN youjia.orders_distribute d ON d.order_id = bo.id
                LEFT JOIN youjia.users u ON u.third_id = d.third_user_id
                WHERE bo.city_id <> 376
                AND bo.brush_status <> 1
                AND bo.order_type = 0
                AND DATE(bo.create_time) BETWEEN '{st}' AND '{et}'
                AND u.id IS NOT NULL) bo on bo.user_id = it.from_id and it.lodge_id = bo.lodge_id and it.channel_id = bo.order_channel
            left join youjia_common.sales_channel sc on sc.id = it.channel_id
            left join youjia.lod_house lh on lh.lodge_id = it.lodge_id
            left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id
            left join youjia.city on city.id = lh.city_id
            left join youjia.user_org uo on uo.id = lh.work_id and uo.is_delete = 0
            left join youjia.user_org uo2 on uo2.id=uo.parent_id and uo2.is_delete = 0
		    left join youjia.user_org uo3 on uo3.id=uo2.parent_id and uo3.is_delete = 0
            left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
            left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
            LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
            where DATE(it.create_time) BETWEEN '{st}' AND '{et}'
            and it.channel_id in {channel_ids}
            and lh.own_type in {own_type}
            and lh.city_id <> 376
            and it.work_name NOT LIKE '%%五家渠%%'
            -- AND HOUR(it.create_time) BETWEEN '9' AND '20'
            group by lh.own_type,it.id
            with rollup;""".format(st=st, et=et, own_type=own_type, channel_ids=channel_ids)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def im_detail(st, et):
    """
    :param st:
    :param et:
    :return:
    """

    sql = """select
            c.short_name 城市,
            ifnull(uosc.sname,'服务中心为空') 服务中心,
            uo.name 门店,
            u1.realname 销售,
            concat(it.id,"") 对话id ,
            concat(it.lodge_id,"") 房源id,
            sc.name 渠道,
           --  ims.channel_id ,
            if(ims.from_user_type = 1,'客人咨询','管家回复') 消息来源,
            ims.create_time 发送消息时间,
            ims.content 消息内容
            FROM youjia_im.im_msgs ims
            LEFT JOIN youjia_im.im_talks it ON ims.talk_id = it.id
            left join youjia.lod_house lh on lh.lodge_id = it.lodge_id
            left join youjia_common.sales_channel sc on sc.id = ims.channel_id 
            left join youjia.city c on c.id = it.city_id
            left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
            left join youjia.user_org uo on uo.id = lh.work_id
            
            left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id
            where ims.msg_type = 1
            and ims.to_id <> 0
            and ims.channel_id IN (3,20)
            and it.work_name NOT LIKE '%%五家渠%%'
            and DATE(it.create_time) BETWEEN '{st}' AND '{et}'
            AND HOUR(it.create_time) BETWEEN '9' AND '20'
            order by  u1.id ,it.id ,ims.create_time""".format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def im_time_detail(st, et, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    每条对话的时间记录明细
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:
    """
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    if isinstance(channel_ids, str) or isinstance(channel_ids, int):
        channel_ids = '({channel_ids})'.format(channel_ids=channel_ids)
    if isinstance(channel_ids, list):
        if channel_ids.__len__() == 1:
            channel_ids = '({channel_ids})'.format(channel_ids=channel_ids[0])
        else:
            channel_ids = tuple(channel_ids)

    sql = """select 
	case group_concat(distinct A.own_type order by A.own_type)
     when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
     if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct A.short_name)) 城市,
    if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct A.work_name)) 门店,
    if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct A.realname order by A.realname)) 门店销售,
    if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct concat(A.lodge_id,"")))  房源id,
    if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct concat(A.house_id,"")))  房屋ID,
    A.title 房屋名称,
     if(COALESCE(A.it_id,'全国') = '全国','全国',group_concat(distinct A.name)) 渠道,
     A.最早咨询时间 客人第一次咨询时间,
     A.最早回复时间 运营第一次回复时间,
     A.最晚咨询时间 客人最后一次咨询时间,
     A.最晚回复时间 运营最后一次回复时间 
    from(
            select
            it.id it_id,
            uo.name work_name,
            uosc.sname 服务中心,
            lh.own_type,
            lh.title,
            u1.realname,
            uo.id work_id,
            it.lodge_id,
            lh.id house_id,
            lh.city_id,
            c.short_name,
            sc.name,
            ims.channel_id ,
            count(distinct if(ims.from_user_type = 1,it.id,null)) 咨询数,
            count(distinct if(ims.from_user_type = 2,it.id,null)) 回复数,
            min(if(ims.from_user_type = 1,ims.create_time,null)) 最早咨询时间,
            min(if(ims.from_user_type = 2,ims.create_time,null)) 最早回复时间,
            max(if(ims.from_user_type = 1,ims.create_time,null)) 最晚咨询时间,
            max(if(ims.from_user_type = 2,ims.create_time,null)) 最晚回复时间 
            
            FROM youjia_im.im_talks it
            LEFT JOIN youjia_im.im_msgs ims ON ims.talk_id = it.id
            LEFT JOIN youjia_im.im_msgs ims2 ON ims2.talk_id = it.msg_id
            left join youjia_common.sales_channel sc on sc.id = ims.channel_id 
            left join youjia.lod_house lh on lh.lodge_id = it.lodge_id
            left join youjia.city c on c.id = it.city_id
            left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
            left join youjia.user_org uo on uo.id = lh.work_id
            left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id
            where it.channel_id in {channel_ids}
            and lh.own_type in {own_type}
            and it.work_name NOT LIKE '%五家渠%'
            and lh.city_id <> 376
            and DATE(it.create_time) BETWEEN '{st}' AND '{et}'
            AND HOUR(it.create_time) BETWEEN '9' AND '20'
            group by it.id) A
    group by A.own_type , A.it_id
    with rollup;""".format(st=st, et=et, channel_ids=channel_ids, own_type=own_type)
    return pd.read_sql_query(sql, db_util.con_youjia)


def order_people_house(st, et, own_type=(1, 2, 3), have_long=False):
    """

    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :return:
    """
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql_add = "" if have_long else 'and bo.order_type <> 3'
    sql = """select 
            group_concat(distinct A.城市)  城市,
             case group_concat(distinct A.own_type order by A.own_type)
             when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
             A.房屋id 房屋ID,
            count(distinct A.下单人,下单人电话 ) 下单人的数量
            from (select 
            c.short_name 城市,
            uo.name 门店,
            lh.own_type,
            ifnull(uosc.sname,'服务中心为空') 服务中心,
            group_concat(distinct u1.realname) 销售,
            group_concat(distinct u2.realname) 管家,
            concat(lh.id,"") 房屋id,
            concat(bo.id,"") 订单id,
            oe.tenant_name 下单人, 
            oe.tenant_mobile 下单人电话
            FROM youjia.orders bo
            LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
            left join youjia.order_detail od on od.order_id = bo.id
            LEFT JOIN youjia.city c ON c.id=bo.city_id
            left join youjia.lod_house lh on lh.id = od.house_id
            left join youjia.user_org uo on uo.id = lh.work_id
            left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
            left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
            left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
            LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
            WHERE bo.city_id <> 376
            and bo.cancel_status not in (1,2,9)
            and od.house_id is not null 
            and od.house_id <> 0 
            and od.house_id <> ''
            AND bo.brush_status <> 1 
            and bo.special_status <> 3 
            and bo.special_status <> 1 
            {sql_add}
            and lh.own_type in {own_type}
            AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5))
            AND DATE(bo.create_time) between '{st}' AND '{et}'
            and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
            and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
            and oe.tenant_mobile is not null
            GROUP BY bo.id) A
            group by A.own_type,A.房屋id """.format(st=st, et=et, own_type=own_type, sql_add=sql_add)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def order_people_online(st, et, own_type=(1, 2, 3)):
    """

    :param st:
    :param et:
    :param own_type:
    :return:
    """
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            if(COALESCE(A.销售,'全国') = '全国','全国',group_concat(distinct A.城市))  城市,
            case group_concat(distinct A.own_type order by A.own_type)
                 when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,  
            if(COALESCE(A.销售,'全国') = '全国','全国',group_concat(distinct A.门店))  门店,
            if(COALESCE(A.销售,'全国') = '全国','全国',group_concat(distinct A.服务中心)) 服务中心,
            COALESCE(A.销售,'全国') 销售 ,
            if(COALESCE(A.销售,'全国') = '全国','全国',group_concat(distinct A.管家 order by A.管家)) 管家,
            count(distinct A.下单人,下单人电话 ) 下单人的数量
            from (select 
            c.short_name 城市,
            lh.own_type,
            ifnull(uo.name ,'门店为空') 门店,
            ifnull(uosc.sname,'服务中心为空') 服务中心,
            u1.realname 销售,
            u2.realname 管家,
            concat(lh.id,"") 房屋id,
            concat(bo.id,"") 订单id,
            oe.tenant_name 下单人, 
            oe.tenant_mobile 下单人电话
            FROM youjia.orders bo
            LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
            left join youjia.order_detail od on od.order_id = bo.id
            LEFT JOIN youjia.city c ON c.id=bo.city_id
            left join youjia.lod_house lh on lh.id = od.house_id
            left join youjia.user_org uo on uo.id = lh.work_id
            left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
            left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
            left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
            LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
            WHERE bo.city_id <> 376
            and lh.city_id <> 376
            and lh.own_type in {own_type}
            and bo.cancel_status not in (1,2,9)
            and od.house_id is not null 
            and od.house_id <> 0 
            and od.house_id <> ''
            AND bo.brush_status <> 1 
            and bo.special_status <> 3 
            and bo.special_status <> 1 
            AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5))
            AND DATE(bo.create_time) between '{st}' AND '{et}'
            and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
            and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
            and oe.tenant_mobile is not null
            GROUP BY bo.id) A
            group by  A.own_type,A.销售 with rollup """.format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def order_people_offline(st, et, own_type=(1, 2, 3)):
    """

    :param st:
    :param et:
    :param own_type:
    :return:
    """
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            if(COALESCE(A.管家,'全国') = '全国','全国',group_concat(distinct A.城市)) 城市,
            case group_concat(distinct A.own_type order by A.own_type)
                 when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司, 
            if(COALESCE(A.管家,'全国') = '全国','全国',group_concat(distinct A.门店))  门店,
            if(COALESCE(A.管家,'全国') = '全国','全国',group_concat(distinct A.服务中心)) 服务中心,
            if(COALESCE(A.管家,'全国') = '全国','全国',group_concat(distinct A.销售 order by A.销售)) 销售 ,
            COALESCE(A.管家,'全国') 管家,
            count(distinct A.下单人,下单人电话 ) 下单人的数量
            from (select 
            c.short_name 城市,
            lh.own_type,
            ifnull(uo.name,'门店为空') 门店,
            ifnull(uosc.sname,'服务中心为空') 服务中心,
            group_concat(distinct u1.realname) 销售,
            group_concat(distinct u2.realname) 管家,
            concat(lh.id,"") 房屋id,
            concat(bo.id,"") 订单id,
            oe.tenant_name 下单人, 
            oe.tenant_mobile 下单人电话
            FROM youjia.orders bo
            LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
            left join youjia.order_detail od on od.order_id = bo.id
            LEFT JOIN youjia.city c ON c.id=bo.city_id
            left join youjia.lod_house lh on lh.id = od.house_id
            left join youjia.user_org uo on uo.id = lh.work_id
            left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
            left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
            left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
            LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
            WHERE bo.city_id <> 376
            and lh.own_type in {own_type}
            and bo.cancel_status not in (1,2,9)
            and od.house_id is not null 
            and od.house_id <> 0 
            and od.house_id <> ''
            AND bo.brush_status <> 1 
            and bo.special_status <> 3 
            and bo.special_status <> 1 
            AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5))
            AND DATE(bo.create_time) between '{st}' AND '{et}'
            and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
            and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
            and oe.tenant_mobile is not null
            GROUP BY bo.id) A
            group by  A.own_type,A.管家 with rollup""".format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def order_people_work(st, et, own_type=(1, 2, 3), have_long=False):
    """

    :param st:
    :param et:
    :param own_type:
    :return:
    """
    sql_add = '' if have_long else 'and bo.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            if(COALESCE(A.门店,'全国') = '全国','全国',group_concat(distinct A.城市)) 城市,
            -- case group_concat(distinct A.own_type order by A.own_type)
            --     when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司, 
             
             COALESCE(A.门店,'全国') 门店,
            count(distinct A.下单人,下单人电话 ) 下单人的数量
            from (select 
            c.short_name 城市,
            lh.own_type,
            ifnull(uo.name,'门店为空') 门店,
            ifnull(uosc.sname,'服务中心为空') 服务中心,
            group_concat(distinct u1.realname) 销售,
            group_concat(distinct u2.realname) 管家,
            concat(lh.id,"") 房屋id,
            concat(bo.id,"") 订单id,
            oe.tenant_name 下单人, 
            oe.tenant_mobile 下单人电话
            FROM youjia.orders bo
            LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
            left join youjia.order_detail od on od.order_id = bo.id
            LEFT JOIN youjia.city c ON c.id=bo.city_id
            left join youjia.lod_house lh on lh.id = od.house_id
            left join youjia.user_org uo on uo.id = lh.work_id
            left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
            left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
            left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
            LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
            WHERE bo.city_id <> 376
            and lh.own_type in {own_type}
            and bo.cancel_status not in (1,2,9)
            and od.house_id is not null 
            and od.house_id <> 0 
            and od.house_id <> ''
            AND bo.brush_status <> 1 
            and bo.special_status <> 3 
            and bo.special_status <> 1 
            {sql_add}
            AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5))
            AND DATE(bo.create_time) between '{st}' AND '{et}'
            and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
            and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
            and oe.tenant_mobile is not null
            GROUP BY bo.id) A
            group by   A.门店 with rollup""".format(st=st, et=et, own_type=own_type, sql_add=sql_add)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def order_people_service(st, et, own_type=(1, 2, 3)):
    """

    :param st:
    :param et:
    :param own_type:
    :return:
    """
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            if(COALESCE(A.服务中心,'全国') = '全国','全国',group_concat(distinct A.城市))   城市,
            case group_concat(distinct A.own_type order by A.own_type)
                 when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司, 
            COALESCE(A.服务中心,'全国') 服务中心,
            count(distinct A.下单人,下单人电话 ) 下单人的数量
            from (select 
            c.short_name 城市,
            uo.name 门店,
            lh.own_type,
            ifnull(uosc.sname,'服务中心为空') 服务中心,
            group_concat(distinct u1.realname) 销售,
            group_concat(distinct u2.realname) 管家,
            concat(lh.id,"") 房屋id,
            concat(bo.id,"") 订单id,
            oe.tenant_name 下单人, 
            oe.tenant_mobile 下单人电话
            FROM youjia.orders bo
            LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
            left join youjia.order_detail od on od.order_id = bo.id
            LEFT JOIN youjia.city c ON c.id=bo.city_id
            left join youjia.lod_house lh on lh.id = od.house_id
            left join youjia.user_org uo on uo.id = lh.work_id
            left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
            left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
            left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
            LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
            WHERE bo.city_id <> 376
            and lh.own_type in {own_type}
            and bo.cancel_status not in (1,2,9)
            and od.house_id is not null 
            and od.house_id <> 0 
            and od.house_id <> ''
            AND bo.brush_status <> 1 
            and bo.special_status <> 3 
            and bo.special_status <> 1 
            AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5))
            AND DATE(bo.create_time) between '{st}' AND '{et}'
            and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
            and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
            and oe.tenant_mobile is not null
            GROUP BY bo.id) A
            group by  A.own_type,A.服务中心 with rollup""".format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def order_people_city(st, et, own_type=(1, 2, 3)):
    """
    服务中心下单数
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            case group_concat(distinct A.own_type order by A.own_type)
            when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            COALESCE(A.城市,'全国')  城市,
            
            count(distinct A.下单人,下单人电话 ) 下单人的数量
            from (select 
            c.short_name 城市,
            lh.own_type,
            uo.name 门店,
            ifnull(uosc.sname,'服务中心为空') 服务中心,
            group_concat(distinct u1.realname) 销售,
            group_concat(distinct u2.realname) 管家,
            concat(lh.id,"") 房屋id,
            concat(bo.id,"") 订单id,
            oe.tenant_name 下单人, 
            oe.tenant_mobile 下单人电话
            FROM youjia.orders bo
            LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
            left join youjia.order_detail od on od.order_id = bo.id
            LEFT JOIN youjia.city c ON c.id=bo.city_id
            left join youjia.lod_house lh on lh.id = od.house_id
            left join youjia.user_org uo on uo.id = lh.work_id
            left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
            left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
            left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
            LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
            WHERE bo.city_id <> 376
            and lh.own_type in {own_type}
            and od.house_id is not null 
            and od.house_id <> 0 
            and od.house_id <> ''
            AND bo.brush_status <> 1 
            and bo.special_status <> 3 
            and bo.special_status <> 1 
            and bo.cancel_status not in (1,2,9)
            AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5))
            AND DATE(bo.create_time) between '{st}' AND '{et}'
            and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
            and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
            and oe.tenant_mobile is not null
            GROUP BY bo.id) A
            group by A.own_type,A.城市 
            with rollup""".format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def order_people_district(st, et, own_type=(1, 2, 3)):
    """
    服务中心下单数
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            COALESCE(A.大区,'全国')  大区,
            count(distinct A.下单人,下单人电话 ) 下单人的数量
            from (select 
            c.short_name 城市,
            uo.name 大区,
            lh.own_type,
            uo.name 门店,
            ifnull(uosc.sname,'服务中心为空') 服务中心,
            group_concat(distinct u1.realname) 销售,
            group_concat(distinct u2.realname) 管家,
            concat(lh.id,"") 房屋id,
            concat(bo.id,"") 订单id,
            oe.tenant_name 下单人, 
            oe.tenant_mobile 下单人电话
            FROM youjia.orders bo
            LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
            left join youjia.order_detail od on od.order_id = bo.id
            LEFT JOIN youjia.city c ON c.id=bo.city_id
            left join youjia.lod_house lh on lh.id = od.house_id
            left join youjia.user_org uo on uo.id = lh.work_id and uo.is_delete = 0
            left join youjia.user_org uo2 on uo2.id=uo.parent_id and uo2.is_delete = 0
		    left join youjia.user_org uo3 on uo3.id=uo2.parent_id and uo3.is_delete = 0
            left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
            left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
            left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
            LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
            WHERE bo.city_id <> 376
            and lh.own_type in {own_type}
            and od.house_id is not null 
            and od.house_id <> 0 
            and od.house_id <> ''
            AND bo.brush_status <> 1 
            and bo.special_status <> 3 
            and bo.special_status <> 1 
            and bo.cancel_status not in (1,2,9)
            AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5))
            AND DATE(bo.create_time) between '{st}' AND '{et}'
            and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
            and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
            and oe.tenant_mobile is not null
            GROUP BY bo.id) A
            group by A.大区 
            with rollup""".format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def refund_detail(st, et, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """
            select 
            case group_concat(distinct lh.own_type order by lh.own_type)
                when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            if(COALESCE(bo.id,'全国')='全国','全国',group_concat(distinct c.short_name))  城市,
            if(COALESCE(bo.id,'全国')='全国','全国',group_concat(distinct uo.name)) 门店,
            if(COALESCE(bo.id,'全国')='全国','全国',group_concat(distinct ifnull(uosc.sname,'服务中心为空'))) 服务中心,
            lh.address 房屋地址,
            u1.realname 销售,
            u2.realname 管家,
            concat(lh.id,"") 房屋id,
            concat(bo.id,"") 订单id,
            bo.cancel_reason 取消原因,
            concat(round(bo.actual_total/100, 2), '') 退款订单应收金额,
            concat(round(bo.refund_total/100, 2), '') 退款订单退款金额,
            oe.tenant_name 预订人姓名, 
            oe.tenant_mobile 预订人电话,
            case bo.cancel_status 
            when 0 then '未取消'
            when 1 then '房客取消支付超时'
            when 2 then '房客取消未付款'
            when 3 then '房客取消全额退款'
            when 4 then '房客取消部分扣款'
            when 5 then '房客取消全额扣款'
            when 6 then '管家拒绝订单取消'
            when 7 then '管家拒绝全额退款'
            when 8 then '管家拒绝确认超时'
            when 9 then '无房了'
            when 10 then '客服介入'
            else ""
            end 取消状态,           
            date(bo.checkin_day) 入住时间,
            date(bo.checkout_day) 离店时间,
            bo.create_time 下单时间,
            bo.cancel_time 取消时间,
            bo.refund_time 退款成功时间
            from (select 
                distinct oe.tenant_name, oe.tenant_mobile
                FROM youjia.orders bo
                LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
                left join youjia.order_detail od on od.order_id = bo.id
                LEFT JOIN youjia.city c ON c.id=bo.city_id
                WHERE bo.city_id <> 376
                and od.house_id is not null 
                and od.house_id <> 0 
                and od.house_id <> ''
                AND bo.brush_status <> 1 
                and bo.cancel_status not in (1,2,9)
                and bo.special_status <> 3 
                and bo.special_status <> 1 
                AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5))
                AND DATE(bo.create_time) between '{st}' AND '{et}'
                and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                and oe.tenant_mobile is not null
                -- and oe.tenant_mobile = '18618371944'
            GROUP BY oe.tenant_name, oe.tenant_mobile
            having count(distinct if (bo.cancel_status =0,bo.id,null)) = 0) A
            LEFT JOIN youjia.order_extend oe ON oe.tenant_name=A.tenant_name and oe.tenant_mobile =A.tenant_mobile
            
            left join youjia.orders bo  ON oe.order_id = bo.id
            left join youjia.order_detail od on od.order_id = bo.id
            LEFT JOIN youjia.city c ON c.id=bo.city_id
            left join youjia.lod_house lh on lh.id = od.house_id
            left join youjia.user_org uo on uo.id = lh.work_id
            left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
            left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
            left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
            LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
            where DATE(bo.create_time) between '{st}' AND '{et}'
            and lh.own_type in {own_type}
            and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
            and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
            group by lh.own_type,bo.id
                """.format(st=st, et=et, own_type=own_type)
    df_g = pd.read_sql_query(sql, db_util.youjia_13_sjz_db)
    df_g = df_util.df_set_index(df_g, '城市')
    return df_g


def refund_house(st=None, et=None, own_type=(1, 2, 3), have_long=False):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql_add = '' if have_long else 'and bo.order_type <> 3'
    sql = """select 
            case group_concat(distinct A.own_type order by A.own_type)
                when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            group_concat(distinct A.城市 ) 城市,
            A.房屋id 房屋ID ,
            count(distinct A.下单人,下单人电话 ) 退款人的数量
            from (select 
                c.short_name 城市,
                uo.name 门店,
                lh.own_type,
                group_concat(distinct ifnull(uosc.sname,'服务中心为空')) 服务中心,
                u1.realname 销售,
                u2.realname 管家,
                concat(lh.id,"") 房屋id,
                concat(bo.id,"") 订单id,
                oe.tenant_name 下单人, 
                oe.tenant_mobile 下单人电话,
                case bo.cancel_status 
                when 0 then '未取消'
                when 1 then '房客取消支付超时'
                when 2 then '房客取消未付款'
                when 3 then '房客取消全额退款'
                when 4 then '房客取消部分扣款'
                when 5 then '房客取消全额扣款'
                when 6 then '管家拒绝订单取消'
                when 7 then '管家拒绝全额退款'
                when 8 then '管家拒绝确认超时'
                when 9 then '无房了'
                when 10 then '客服介入'
                else ""
                end 取消状态,           
                date(bo.checkin_day) 入住时间,
                date(bo.checkout_day) 离店时间,
                bo.create_time 下单时间,
                concat(round(bo.actual_total/100, 2), '') 退款订单应收金额,
                concat(round(bo.refund_total/100, 2), '') 退款订单退款金额
                from (select 
                    distinct oe.tenant_name, oe.tenant_mobile
                    FROM youjia.orders bo
                    LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
                    left join youjia.order_detail od on od.order_id = bo.id
                    LEFT JOIN youjia.city c ON c.id=bo.city_id
                    WHERE bo.city_id <> 376
                    and bo.cancel_status not in (1,2,9)
                    and od.house_id is not null 
                    and od.house_id <> 0 
                    and od.house_id <> ''
                    AND bo.brush_status <> 1 
                    and bo.special_status <> 3 
                    and bo.special_status <> 1 
                    AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5))
                    AND DATE(bo.create_time) between '{st}' AND '{et}'
                    and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                    and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                    and oe.tenant_mobile is not null
                    -- and oe.tenant_mobile = '18618371944'
                GROUP BY oe.tenant_name, oe.tenant_mobile
                having count(distinct if (bo.cancel_status =0,bo.id,null)) = 0) A
                LEFT JOIN youjia.order_extend oe ON oe.tenant_name=A.tenant_name and oe.tenant_mobile =A.tenant_mobile
                
                left join youjia.orders bo  ON oe.order_id = bo.id
                left join youjia.order_detail od on od.order_id = bo.id
                LEFT JOIN youjia.city c ON c.id=bo.city_id
                left join youjia.lod_house lh on lh.id = od.house_id
                left join youjia.user_org uo on uo.id = lh.work_id
                left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
                left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
                LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
                left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
                LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
                where DATE(bo.create_time) between '{st}' AND '{et}'
                and lh.own_type in {own_type}
                {sql_add}
                and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                group by bo.id) A
            group by  A.own_type,A.房屋id 
            
            """.format(st=st, et=et, own_type=own_type, sql_add=sql_add)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def refund_online(st=None, et=None, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            case group_concat(distinct A.own_type order by A.own_type)
                when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            if(COALESCE(A.销售 ,'全国')='全国','全国',group_concat(distinct A.城市 )) 城市,
          --  group_concat(distinct A.门店)  门店,
          --  group_concat(distinct A.服务中心) 服务中心,
            COALESCE(A.销售 ,'全国' ) 销售 ,
         --   group_concat(distinct A.管家) 管家,
            count(distinct A.下单人,下单人电话 ) 退款人的数量,
            A.退款订单应收金额 退款订单应收金额,
            A.退款订单退款金额 退款订单退款金额
            from (select 
                c.short_name 城市,
                uo.name 门店,
                group_concat(distinct ifnull(uosc.sname,'服务中心为空')) 服务中心,
                u1.realname 销售,
                u2.realname 管家,
                lh.own_type,
                concat(lh.id,"") 房屋id,
                concat(bo.id,"") 订单id,
                oe.tenant_name 下单人, 
                oe.tenant_mobile 下单人电话,
                case bo.cancel_status 
                when 0 then '未取消'
                when 1 then '房客取消支付超时'
                when 2 then '房客取消未付款'
                when 3 then '房客取消全额退款'
                when 4 then '房客取消部分扣款'
                when 5 then '房客取消全额扣款'
                when 6 then '管家拒绝订单取消'
                when 7 then '管家拒绝全额退款'
                when 8 then '管家拒绝确认超时'
                when 9 then '无房了'
                when 10 then '客服介入'
                else ""
                end 取消状态,           
                date(bo.checkin_day) 入住时间,
                date(bo.checkout_day) 离店时间,
                concat(round(bo.actual_total/100, 2), '') 退款订单应收金额,
                concat(round(bo.refund_total/100, 2), '') 退款订单退款金额,
                bo.create_time 下单时间
                from (select 
                    distinct oe.tenant_name, oe.tenant_mobile
                    FROM youjia.orders bo
                    LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
                    left join youjia.order_detail od on od.order_id = bo.id
                    LEFT JOIN youjia.city c ON c.id=bo.city_id
                    WHERE bo.city_id <> 376
                    
                    and bo.cancel_status not in (1,2,9)
                    and od.house_id is not null 
                    and od.house_id <> 0 
                    and od.house_id <> ''
                    AND bo.brush_status <> 1 
                    and bo.special_status <> 3 
                    and bo.special_status <> 1 
                    AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5))
                    AND DATE(bo.create_time) between '{st}' AND '{et}'
                    and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                    and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                    and oe.tenant_mobile is not null
                    -- and oe.tenant_mobile = '18618371944'
                GROUP BY oe.tenant_name, oe.tenant_mobile
                having count(distinct if (bo.cancel_status =0,bo.id,null)) = 0) A
                LEFT JOIN youjia.order_extend oe ON oe.tenant_name=A.tenant_name and oe.tenant_mobile =A.tenant_mobile
                
                left join youjia.orders bo  ON oe.order_id = bo.id
                left join youjia.order_detail od on od.order_id = bo.id
                LEFT JOIN youjia.city c ON c.id=bo.city_id
                left join youjia.lod_house lh on lh.id = od.house_id
                left join youjia.user_org uo on uo.id = lh.work_id
                left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
                left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
                LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
                left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
                LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
                where DATE(bo.create_time) between '{st}' AND '{et}'
                and lh.own_type in {own_type}
                and lh.city_id <> 376
                and bo.city_id <> 376
                and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                group by bo.id) A
            group by A.own_type ,A.销售 
            
            """.format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def refund_offline(st=None, et=None, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            case group_concat(distinct A.own_type order by A.own_type)
                when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
             if(COALESCE(A.管家 ,'全国')='全国','全国',group_concat(distinct A.城市 )) 城市,
            COALESCE(A.管家 ,'全国') 管家,
            count(distinct A.下单人,下单人电话 ) 退款人的数量
            from (select 
                c.short_name 城市,
                uo.name 门店,
                lh.own_type,
                group_concat(distinct ifnull(uosc.sname,'服务中心为空')) 服务中心,
                u1.realname 销售,
                u2.realname 管家,
                concat(lh.id,"") 房屋id,
                concat(bo.id,"") 订单id,
                oe.tenant_name 下单人, 
                oe.tenant_mobile 下单人电话,
                case bo.cancel_status 
                when 0 then '未取消'
                when 1 then '房客取消支付超时'
                when 2 then '房客取消未付款'
                when 3 then '房客取消全额退款'
                when 4 then '房客取消部分扣款'
                when 5 then '房客取消全额扣款'
                when 6 then '管家拒绝订单取消'
                when 7 then '管家拒绝全额退款'
                when 8 then '管家拒绝确认超时'
                when 9 then '无房了'
                when 10 then '客服介入'
                else ""
                end 取消状态,           
                date(bo.checkin_day) 入住时间,
                date(bo.checkout_day) 离店时间,
                bo.create_time 下单时间,
                concat(round(bo.actual_total/100, 2), '') 退款订单应收金额,
                concat(round(bo.refund_total/100, 2), '') 退款订单退款金额
                from (select 
                    distinct oe.tenant_name, oe.tenant_mobile
                    FROM youjia.orders bo
                    LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
                    left join youjia.order_detail od on od.order_id = bo.id
                    LEFT JOIN youjia.city c ON c.id=bo.city_id
                    WHERE bo.city_id <> 376
                    and bo.cancel_status not in (1,2,9)
                    and od.house_id is not null 
                    and od.house_id <> 0 
                    and od.house_id <> ''
                    AND bo.brush_status <> 1 
                    and bo.special_status <> 3 
                    and bo.special_status <> 1 
                    AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5))
                    AND DATE(bo.create_time) between '{st}' AND '{et}'
                    and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                    and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                    and oe.tenant_mobile is not null
                    -- and oe.tenant_mobile = '18618371944'
                GROUP BY oe.tenant_name, oe.tenant_mobile
                having count(distinct if (bo.cancel_status =0,bo.id,null)) = 0) A
                LEFT JOIN youjia.order_extend oe ON oe.tenant_name=A.tenant_name and oe.tenant_mobile =A.tenant_mobile
                
                left join youjia.orders bo  ON oe.order_id = bo.id
                left join youjia.order_detail od on od.order_id = bo.id
                LEFT JOIN youjia.city c ON c.id=bo.city_id
                left join youjia.lod_house lh on lh.id = od.house_id
                left join youjia.user_org uo on uo.id = lh.work_id
                left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
                left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
                LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
                left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
                LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
                where DATE(bo.create_time) between '{st}' AND '{et}'
                and lh.own_type in {own_type}
                and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                group by bo.id) A
            group by A.own_type ,A.管家 
            
            """.format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def refund_work(st=None, et=None, own_type=(1, 2, 3), have_long=True):
    sql_add = "" if have_long else 'and bo.order_type <> 3'
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            -- case group_concat(distinct A.own_type order by A.own_type)
            --    when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            if(COALESCE(A.门店 ,'全国')='全国','全国',group_concat(distinct A.城市 )) 城市,
            COALESCE(A.门店 ,'全国') 门店,
            count(distinct A.下单人,下单人电话 ) 退款人的数量,
            sum(A.退款订单应收金额) 退款订单应收金额,
            sum(A.退款订单退款金额) 退款订单退款金额
            from (select 
                c.short_name 城市,
                uo.name 门店,
                group_concat(distinct ifnull(uosc.sname,'服务中心为空')) 服务中心,
                u1.realname 销售,
                u2.realname 管家,
                lh.own_type,
                concat(lh.id,"") 房屋id,
                concat(bo.id,"") 订单id,
                oe.tenant_name 下单人, 
                oe.tenant_mobile 下单人电话,
                case bo.cancel_status 
                when 0 then '未取消'
                when 1 then '房客取消支付超时'
                when 2 then '房客取消未付款'
                when 3 then '房客取消全额退款'
                when 4 then '房客取消部分扣款'
                when 5 then '房客取消全额扣款'
                when 6 then '管家拒绝订单取消'
                when 7 then '管家拒绝全额退款'
                when 8 then '管家拒绝确认超时'
                when 9 then '无房了'
                when 10 then '客服介入'
                else ""
                end 取消状态,           
                date(bo.checkin_day) 入住时间,
                date(bo.checkout_day) 离店时间,
                concat(round(bo.actual_total/100, 2), '') 退款订单应收金额,
                concat(round(bo.refund_total/100, 2), '') 退款订单退款金额,
                bo.create_time 下单时间
                from (select 
                    distinct oe.tenant_name, oe.tenant_mobile
                    FROM youjia.orders bo
                    LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
                    left join youjia.order_detail od on od.order_id = bo.id
                    LEFT JOIN youjia.city c ON c.id=bo.city_id
                    WHERE bo.city_id <> 376
                    and bo.cancel_status not in (1,2,9)
                    and od.house_id is not null 
                    and od.house_id <> 0 
                    and od.house_id <> ''
                    AND bo.brush_status <> 1 
                    and bo.special_status <> 3 
                    and bo.special_status <> 1 
                    {sql_add}
                    AND (bo.pay_state = 1  OR (bo.pay_state =0 AND  bo.order_channel=5))
                    AND DATE(bo.create_time) between '{st}' AND '{et}'
                    and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                    and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                    and oe.tenant_mobile is not null
                    -- and oe.tenant_mobile = '18618371944'
                GROUP BY oe.tenant_name, oe.tenant_mobile
                having count(distinct if (bo.cancel_status = 0,bo.id,null)) = 0) A
                LEFT JOIN youjia.order_extend oe ON oe.tenant_name=A.tenant_name and oe.tenant_mobile =A.tenant_mobile

                left join youjia.orders bo  ON oe.order_id = bo.id
                left join youjia.order_detail od on od.order_id = bo.id
                LEFT JOIN youjia.city c ON c.id=bo.city_id
                left join youjia.lod_house lh on lh.id = od.house_id
                left join youjia.user_org uo on uo.id = lh.work_id
                left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
                left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
                LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
                left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
                LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
                where DATE(bo.create_time) between '{st}' AND '{et}'
                and lh.own_type in {own_type}
                {sql_add}
                and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                group by bo.id) A
            group by  A.门店 
            with rollup

            """.format(st=st, et=et, own_type=own_type, sql_add=sql_add)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def refund_service(st=None, et=None, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            case group_concat(distinct A.own_type order by A.own_type)
                when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            if(COALESCE(A.服务中心 ,'全国')='全国','全国',group_concat(distinct A.城市 )) 城市,
            COALESCE(A.服务中心 ,'全国')  服务中心,
            count(distinct A.下单人,下单人电话 ) 退款人的数量
            from (select 
                c.short_name 城市,
                uo.name 门店,
                lh.own_type,
                group_concat(distinct ifnull(uosc.sname,'服务中心为空')) 服务中心,
                u1.realname 销售,
                u2.realname 管家,
                concat(lh.id,"") 房屋id,
                concat(bo.id,"") 订单id,
                oe.tenant_name 下单人, 
                oe.tenant_mobile 下单人电话,
                case bo.cancel_status 
                when 0 then '未取消'
                when 1 then '房客取消支付超时'
                when 2 then '房客取消未付款'
                when 3 then '房客取消全额退款'
                when 4 then '房客取消部分扣款'
                when 5 then '房客取消全额扣款'
                when 6 then '管家拒绝订单取消'
                when 7 then '管家拒绝全额退款'
                when 8 then '管家拒绝确认超时'
                when 9 then '无房了'
                when 10 then '客服介入'
                else ""
                end 取消状态,           
                date(bo.checkin_day) 入住时间,
                date(bo.checkout_day) 离店时间,
                bo.create_time 下单时间
                from (select 
                    distinct oe.tenant_name, oe.tenant_mobile
                    FROM youjia.orders bo
                    LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
                    left join youjia.order_detail od on od.order_id = bo.id
                    LEFT JOIN youjia.city c ON c.id=bo.city_id
                    WHERE bo.city_id <> 376
                    and bo.cancel_status not in (1,2,9)
                    and od.house_id is not null 
                    and od.house_id <> 0 
                    and od.house_id <> ''
                    AND bo.brush_status <> 1 
                    and bo.special_status <> 3 
                    and bo.special_status <> 1 
                    AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5))
                    AND DATE(bo.create_time) between '{st}' AND '{et}'
                    and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                    and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                    and oe.tenant_mobile is not null
                    -- and oe.tenant_mobile = '18618371944'
                GROUP BY oe.tenant_name, oe.tenant_mobile
                having count(distinct if (bo.cancel_status =0,bo.id,null)) = 0) A
                LEFT JOIN youjia.order_extend oe ON oe.tenant_name=A.tenant_name and oe.tenant_mobile =A.tenant_mobile

                left join youjia.orders bo  ON oe.order_id = bo.id
                left join youjia.order_detail od on od.order_id = bo.id
                LEFT JOIN youjia.city c ON c.id=bo.city_id
                left join youjia.lod_house lh on lh.id = od.house_id
                left join youjia.user_org uo on uo.id = lh.work_id and uo.is_delete = 0
                left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
                left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
                LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
                left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
                LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
                where DATE(bo.create_time) between '{st}' AND '{et}'
                and lh.own_type in {own_type}
                and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                group by bo.id) A
            group by A.own_type ,A.服务中心 

            """.format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def refund_city(st=None, et=None, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
            case group_concat(distinct A.own_type order by A.own_type)
            when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            COALESCE(A.城市,'全国')  城市,
           
            count(distinct A.下单人,下单人电话 ) 退款人的数量
            from (select 
                c.short_name 城市,
                lh.own_type,
                uo.name 门店,
                group_concat(distinct ifnull(uosc.sname,'服务中心为空')) 服务中心,
                u1.realname 销售,
                u2.realname 管家,
                concat(lh.id,"") 房屋id,
                concat(bo.id,"") 订单id,
                oe.tenant_name 下单人, 
                oe.tenant_mobile 下单人电话,
                case bo.cancel_status 
                when 0 then '未取消'
                when 1 then '房客取消支付超时'
                when 2 then '房客取消未付款'
                when 3 then '房客取消全额退款'
                when 4 then '房客取消部分扣款'
                when 5 then '房客取消全额扣款'
                when 6 then '管家拒绝订单取消'
                when 7 then '管家拒绝全额退款'
                when 8 then '管家拒绝确认超时'
                when 9 then '无房了'
                when 10 then '客服介入'
                else ""
                end 取消状态,           
                date(bo.checkin_day) 入住时间,
                date(bo.checkout_day) 离店时间,
                bo.create_time 下单时间
                from (select 
                    distinct oe.tenant_name, oe.tenant_mobile
                    FROM youjia.orders bo
                    LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
                    left join youjia.order_detail od on od.order_id = bo.id
                    LEFT JOIN youjia.city c ON c.id=bo.city_id
                    WHERE bo.city_id <> 376
                    and bo.cancel_status not in (1,2,9)
                    and od.house_id is not null 
                    and od.house_id <> 0 
                    and od.house_id <> ''
                    AND bo.brush_status <> 1 
                    and bo.special_status <> 3 
                    and bo.special_status <> 1 
                    AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5))
                    AND DATE(bo.create_time) between '{st}' AND '{et}'
                    and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                    and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                    and oe.tenant_mobile is not null
                    -- and oe.tenant_mobile = '18618371944'
                GROUP BY oe.tenant_name, oe.tenant_mobile
                having count(distinct if (bo.cancel_status =0,bo.id,null)) = 0) A
                LEFT JOIN youjia.order_extend oe ON oe.tenant_name=A.tenant_name and oe.tenant_mobile =A.tenant_mobile

                left join youjia.orders bo  ON oe.order_id = bo.id
                left join youjia.order_detail od on od.order_id = bo.id
                LEFT JOIN youjia.city c ON c.id=bo.city_id
                left join youjia.lod_house lh on lh.id = od.house_id
                left join youjia.user_org uo on uo.id = lh.work_id
                left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
                left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
                LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
                left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
                LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
                where lh.own_type in {own_type} 
                and DATE(bo.create_time) between '{st}' AND '{et}'
                and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                group by bo.id) A
            group by A.own_type,A.城市  
            with rollup
            """.format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def refund_district(st=None, et=None, own_type=(1, 2, 3)):
    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    sql = """select 
             
            COALESCE(A.大区,'全国')  大区,
           
            count(distinct A.下单人,下单人电话 ) 退款人的数量
            from (select 
                uo3.name 大区,
                c.short_name 城市,
                lh.own_type,
                uo.name 门店,
                group_concat(distinct ifnull(uosc.sname,'服务中心为空')) 服务中心,
                u1.realname 销售,
                u2.realname 管家,
                concat(lh.id,"") 房屋id,
                concat(bo.id,"") 订单id,
                oe.tenant_name 下单人, 
                oe.tenant_mobile 下单人电话,
                case bo.cancel_status 
                when 0 then '未取消'
                when 1 then '房客取消支付超时'
                when 2 then '房客取消未付款'
                when 3 then '房客取消全额退款'
                when 4 then '房客取消部分扣款'
                when 5 then '房客取消全额扣款'
                when 6 then '管家拒绝订单取消'
                when 7 then '管家拒绝全额退款'
                when 8 then '管家拒绝确认超时'
                when 9 then '无房了'
                when 10 then '客服介入'
                else ""
                end 取消状态,           
                date(bo.checkin_day) 入住时间,
                date(bo.checkout_day) 离店时间,
                bo.create_time 下单时间
                from (select 
                    distinct oe.tenant_name, oe.tenant_mobile
                    FROM youjia.orders bo
                    LEFT JOIN youjia.order_extend oe ON oe.order_id = bo.id
                    left join youjia.order_detail od on od.order_id = bo.id
                    LEFT JOIN youjia.city c ON c.id=bo.city_id
                    WHERE bo.city_id <> 376
                    and bo.cancel_status not in (1,2,9)
                    and od.house_id is not null 
                    and od.house_id <> 0 
                    and od.house_id <> ''
                    AND bo.brush_status <> 1 
                    and bo.special_status <> 3 
                    and bo.special_status <> 1 
                    AND (bo.pay_state = 1  OR (bo.pay_state = 0 AND  bo.order_channel=5))
                    AND DATE(bo.create_time) between '{st}' AND '{et}'
                    and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                    and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                    and oe.tenant_mobile is not null
                    -- and oe.tenant_mobile = '18618371944'
                GROUP BY oe.tenant_name, oe.tenant_mobile
                having count(distinct if (bo.cancel_status =0,bo.id,null)) = 0) A
                LEFT JOIN youjia.order_extend oe ON oe.tenant_name=A.tenant_name and oe.tenant_mobile =A.tenant_mobile

                left join youjia.orders bo  ON oe.order_id = bo.id
                left join youjia.order_detail od on od.order_id = bo.id
                LEFT JOIN youjia.city c ON c.id=bo.city_id
                left join youjia.lod_house lh on lh.id = od.house_id
                left join youjia.user_org uo on uo.id = lh.work_id and uo.is_delete = 0
                left join youjia.user_org uo2 on uo2.id=uo.parent_id and uo2.is_delete = 0
		        left join youjia.user_org uo3 on uo3.id=uo2.parent_id and uo3.is_delete = 0
                left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id    
                left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
                LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
                left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
                LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
                where lh.own_type in {own_type} 
                and DATE(bo.create_time) between '{st}' AND '{et}'
                and '2019-08-09' not between date(bo.checkin_day) and date(bo.checkout_day)
                and '2019-08-10' not between date(bo.checkin_day) and date(bo.checkout_day)
                group by bo.id) A
            group by A.own_type,A.城市  
            with rollup
            """.format(st=st, et=et, own_type=own_type)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def phone_city():
    phone_city_list = [{"绑定电话": "13077714163", "虚拟号码": "18697990400", "线上销售": "陈政安", "门店": "南宁运营一部", "城市": "南宁"},
                       {"绑定电话": "13077714142", "虚拟号码": "18697997807", "线上销售": "朱晋宏", "门店": "南宁运营一部", "城市": "南宁"},
                       {"绑定电话": "15658327846", "虚拟号码": "13216827569", "线上销售": "金颖", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "15658329149", "虚拟号码": "13216827569", "线上销售": "金颖", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "15658315140", "虚拟号码": "17710562763", "线上销售": "屠鑫霞", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "15658316049", "虚拟号码": "17710562763", "线上销售": "屠鑫霞", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "15658317640", "虚拟号码": "15658295268", "线上销售": "高原", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "15658318043", "虚拟号码": "15658295268", "线上销售": "高原", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "13016420915", "虚拟号码": "18670493247", "线上销售": "丁晶", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "13108930329", "虚拟号码": "15685133553", "线上销售": "张参", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "15607735143", "虚拟号码": "17800827347", "线上销售": "覃显钧", "门店": "桂林分店", "城市": "桂林"},
                       {"绑定电话": "15607735124", "虚拟号码": "17800827347", "线上销售": "覃显钧", "门店": "桂林分店", "城市": "桂林"},
                       {"绑定电话": "15607734991", "虚拟号码": "17800827347", "线上销售": "覃显钧", "门店": "桂林分店", "城市": "桂林"},
                       {"绑定电话": "15607734972", "虚拟号码": "17800827347", "线上销售": "覃显钧", "门店": "桂林分店", "城市": "桂林"},
                       {"绑定电话": "18659612941", "虚拟号码": "19910716982", "线上销售": "郝露露", "门店": "厦门分店", "城市": "厦门"},
                       {"绑定电话": "15583087452", "虚拟号码": "19950079951", "线上销售": "解浩", "门店": "春熙路门店", "城市": "成都"},
                       {"绑定电话": "15595700294", "虚拟号码": "13907603620", "线上销售": "黄玉妙", "门店": "三亚分店", "城市": "三亚"},
                       {"绑定电话": "15595700234", "虚拟号码": "18808997209", "线上销售": "段依雯", "门店": "三亚分店", "城市": "三亚"},
                       {"绑定电话": "15595700204", "虚拟号码": "18808998237", "线上销售": "吴慧艳", "门店": "三亚分店", "城市": "三亚"},
                       {"绑定电话": "15595700184", "虚拟号码": "13700437792", "线上销售": "颜珅", "门店": "三亚分店", "城市": "三亚"},
                       {"绑定电话": "18508843646", "虚拟号码": "18911576235", "线上销售": "太瑞君", "门店": "昆明分店", "城市": "昆明"},
                       {"绑定电话": "18508843642", "虚拟号码": "18911576235", "线上销售": "太瑞君", "门店": "昆明分店", "城市": "昆明"},
                       {"绑定电话": "18508843634", "虚拟号码": "18911576235", "线上销售": "太瑞君", "门店": "昆明分店", "城市": "昆明"},
                       {"绑定电话": "18508843614", "虚拟号码": "18988424036", "线上销售": "晏飞", "门店": "昆明分店", "城市": "昆明"},
                       {"绑定电话": "18508843594", "虚拟号码": "18988424036", "线上销售": "晏飞", "门店": "昆明分店", "城市": "昆明"},
                       {"绑定电话": "18508843574", "虚拟号码": "18988424036", "线上销售": "晏飞", "门店": "昆明分店", "城市": "昆明"},
                       {"绑定电话": "18695759254", "虚拟号码": "19910716953", "线上销售": "沙一夫", "门店": "深圳分店", "城市": "深圳"},
                       {"绑定电话": "18695763543", "虚拟号码": "19910716953", "线上销售": "沙一夫", "门店": "深圳分店", "城市": "深圳"},
                       {"绑定电话": "18698311848", "虚拟号码": "15601020495", "线上销售": "李瑞萍", "门店": "深圳分店", "城市": "深圳"},
                       {"绑定电话": "18698311447", "虚拟号码": "15601020495", "线上销售": "李瑞萍", "门店": "深圳分店", "城市": "深圳"},
                       {"绑定电话": "18659613144", "虚拟号码": "16675161774", "线上销售": "", "门店": "深圳分店", "城市": "深圳"},
                       {"绑定电话": "18659614320", "虚拟号码": "16675161774", "线上销售": "袁倩倩", "门店": "深圳分店", "城市": "深圳"},
                       {"绑定电话": "15530528154", "虚拟号码": "13385326742", "线上销售": "刘悦", "门店": "青岛分店", "城市": "青岛"},
                       {"绑定电话": "15530526684", "虚拟号码": "19910716962", "线上销售": "耿明英", "门店": "青岛分店", "城市": "青岛"},
                       {"绑定电话": "15530526674", "虚拟号码": "18953260714", "线上销售": "邹慧", "门店": "青岛分店", "城市": "青岛"},
                       {"绑定电话": "15530526584", "虚拟号码": "15306481451", "线上销售": "刘悦", "门店": "青岛分店", "城市": "青岛"},
                       {"绑定电话": "15530526574", "虚拟号码": "19910292452", "线上销售": "邹慧", "门店": "青岛分店", "城市": "青岛"},
                       {"绑定电话": "15530526414", "虚拟号码": "18514265820", "线上销售": "毛敬", "门店": "青岛分店", "城市": "青岛"},
                       {"绑定电话": "15530526384", "虚拟号码": "13269534357", "线上销售": "李良", "门店": "青岛分店", "城市": "青岛"},
                       {"绑定电话": "15530526344", "虚拟号码": "19910716951", "线上销售": "黄春素", "门店": "青岛分店", "城市": "青岛"},
                       {"绑定电话": "15530526264", "虚拟号码": "18053277542", "线上销售": "董晓娜", "门店": "青岛分店", "城市": "青岛"},
                       {"绑定电话": "15530526214", "虚拟号码": "17800827134", "线上销售": "李良", "门店": "青岛分店", "城市": "青岛"},
                       {"绑定电话": "15530526144", "虚拟号码": "19910292464", "线上销售": "李杰", "门店": "威海分店", "城市": "威海"},
                       {"绑定电话": "15534084197", "虚拟号码": "15053101617", "线上销售": "钱惠媛", "门店": "济南分店", "城市": "济南"},
                       {"绑定电话": "15534084195", "虚拟号码": "15668373879", "线上销售": "钱惠媛", "门店": "济南分店", "城市": "济南"},
                       {"绑定电话": "13064424632", "虚拟号码": "19910716993", "线上销售": "侯枫烨/周雯", "门店": "郑州分店", "城市": "郑州"},
                       {"绑定电话": "13064424631", "虚拟号码": "19910716993", "线上销售": "侯枫烨/周雯", "门店": "郑州分店", "城市": "郑州"},
                       {"绑定电话": "13064424628", "虚拟号码": "19910716993", "线上销售": "侯枫烨/周雯", "门店": "郑州分店", "城市": "郑州"},
                       {"绑定电话": "13064424624", "虚拟号码": "19910716993", "线上销售": "侯枫烨/周雯", "门店": "郑州分店", "城市": "郑州"},
                       {"绑定电话": "13064424620", "虚拟号码": "19910716993", "线上销售": "侯枫烨/周雯", "门店": "郑州分店", "城市": "郑州"},
                       {"绑定电话": "15523263134", "虚拟号码": "13022928656", "线上销售": "耿文文", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "15523262424", "虚拟号码": "13022928656", "线上销售": "耿文文", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "15523261724", "虚拟号码": "13022928656", "线上销售": "耿文文", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "15523261414", "虚拟号码": "18613839017", "线上销售": "张阳", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "15523262641", "虚拟号码": "18613839017", "线上销售": "张阳", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "15523262496", "虚拟号码": "18516884105", "线上销售": "李劭航", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "15523262471", "虚拟号码": "18516884105", "线上销售": "李劭航", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "15523262148", "虚拟号码": "18613839017", "线上销售": "张阳", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "15658317749", "虚拟号码": "18601199647", "线上销售": "黄婧宇", "门店": "上海分店", "城市": "上海"},
                       {"绑定电话": "15658321148", "虚拟号码": "18515945631", "线上销售": "", "门店": "上海分店", "城市": "上海"},
                       {"绑定电话": "15658321442", "虚拟号码": "13051699329", "线上销售": "董佳丽", "门店": "上海分店", "城市": "上海"},
                       {"绑定电话": "15658323446", "虚拟号码": "19910292046", "线上销售": "陈小勤", "门店": "上海分店", "城市": "上海"},
                       {"绑定电话": "15658310049", "虚拟号码": "19910292187", "线上销售": "马新姣", "门店": "上海分店", "城市": "上海"},
                       {"绑定电话": "15658325341", "虚拟号码": "13051695032", "线上销售": "邹继承", "门店": "上海分店", "城市": "上海"},
                       {"绑定电话": "13016420805", "虚拟号码": "18670066841", "线上销售": "刘捷", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "13016420803", "虚拟号码": "19910716989", "线上销售": "周瀛环", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "13016420802", "虚拟号码": "19910716961", "线上销售": "陈小勤", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "13016420801", "虚拟号码": "19910716937", "线上销售": "陈小勤", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "13016420800", "虚拟号码": "17710927126", "线上销售": "冯佳妮", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "13016420799", "虚拟号码": "18670741154", "线上销售": "杨清宇", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "13016420795", "虚拟号码": "18674845190", "线上销售": "卜亦玄", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "13016420792", "虚拟号码": "18670031554", "线上销售": "龚铭", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "13016420929", "虚拟号码": "19910717003", "线上销售": "夏琨", "门店": "黄鹤楼店", "城市": "武汉"},
                       {"绑定电话": "13016420925", "虚拟号码": "18164246963", "线上销售": "胡均林", "门店": "黄鹤楼店", "城市": "武汉"},
                       {"绑定电话": "13016420923", "虚拟号码": "19910292084", "线上销售": "陈新玲", "门店": "黄鹤楼店", "城市": "武汉"},
                       {"绑定电话": "13016420920", "虚拟号码": "19910292084", "线上销售": "陈新玲", "门店": "黄鹤楼店", "城市": "武汉"},
                       {"绑定电话": "13016420919", "虚拟号码": "19910292084", "线上销售": "陈新玲", "门店": "黄鹤楼店", "城市": "武汉"},
                       {"绑定电话": "13016420918", "虚拟号码": "18062513077", "线上销售": "胡明杰", "门店": "黄鹤楼店", "城市": "武汉"},
                       {"绑定电话": "13016420916", "虚拟号码": "17798226672", "线上销售": "杜经发", "门店": "黄鹤楼店", "城市": "武汉"},
                       {"绑定电话": "15530528604", "虚拟号码": "19910292197", "线上销售": "杨文芳", "门店": "北京CBD店", "城市": "北京"},
                       {"绑定电话": "15530528594", "虚拟号码": "19910292197", "线上销售": "杨文芳", "门店": "北京CBD店", "城市": "北京"},
                       {"绑定电话": "15530528574", "虚拟号码": "19910292197", "线上销售": "杨文芳", "门店": "北京CBD店", "城市": "北京"},
                       {"绑定电话": "15530528544", "虚拟号码": "18610457155", "线上销售": "于海通", "门店": "北京CBD店", "城市": "北京"},
                       {"绑定电话": "15530528504", "虚拟号码": "18610457155", "线上销售": "于海通", "门店": "北京CBD店", "城市": "北京"},
                       {"绑定电话": "15530528434", "虚拟号码": "18610457155", "线上销售": "于海通", "门店": "北京CBD店", "城市": "北京"},
                       {"绑定电话": "15530528084", "虚拟号码": "13051699175", "线上销售": "杨熙", "门店": "北京特殊门店", "城市": "北京"},
                       {"绑定电话": "15530527964", "虚拟号码": "13264351336", "线上销售": "刘晓晨", "门店": "北京特殊门店", "城市": "北京"},
                       {"绑定电话": "15530527894", "虚拟号码": "13264351336", "线上销售": "刘晓晨", "门店": "北京特殊门店", "城市": "北京"},
                       {"绑定电话": "15530527874", "虚拟号码": "15201160243", "线上销售": "韩婷婷", "门店": "北京特殊门店", "城市": "北京"},
                       {"绑定电话": "15530527704", "虚拟号码": "15201160243", "线上销售": "韩婷婷", "门店": "北京特殊门店", "城市": "北京"},
                       {"绑定电话": "15530527694", "虚拟号码": "13264351336", "线上销售": "刘晓晨", "门店": "北京特殊门店", "城市": "北京"},
                       {"绑定电话": "15530527624", "虚拟号码": "15201160243", "线上销售": "韩婷婷", "门店": "北京特殊门店", "城市": "北京"},
                       {"绑定电话": "15530527594", "虚拟号码": "13120219875", "线上销售": "刘赛亚", "门店": "北京特殊门店", "城市": "北京"},
                       {"绑定电话": "15530527554", "虚拟号码": "13120219875", "线上销售": "刘赛亚", "门店": "北京特殊门店", "城市": "北京"},
                       {"绑定电话": "15530527504", "虚拟号码": "13120219875", "线上销售": "刘赛亚", "门店": "北京特殊门店", "城市": "北京"},
                       {"绑定电话": "15530526904", "虚拟号码": "13264358305", "线上销售": "张金霄", "门店": "北京特殊门店", "城市": "北京"},
                       {"绑定电话": "15530526884", "虚拟号码": "13264358305", "线上销售": "张金霄", "门店": "北京特殊门店", "城市": "北京"},
                       {"绑定电话": "15530526734", "虚拟号码": "13264358305", "线上销售": "张金霄", "门店": "北京特殊门店", "城市": "北京"},
                       {"绑定电话": "17124959670", "虚拟号码": "13237102329", "线上销售": "喻烯", "门店": "武汉中南门店", "城市": "武汉"},
                       {"绑定电话": "15658321248", "虚拟号码": "18670332484", "线上销售": "黄天雄", "门店": "上海分店", "城市": "上海"},
                       {"绑定电话": "15523261736", "虚拟号码": "18911575971", "线上销售": "陈诗雨", "门店": "解放碑观音桥门店", "城市": "重庆"},
                       {"绑定电话": "15523261636", "虚拟号码": "18911575971", "线上销售": "陈诗雨", "门店": "解放碑观音桥门店", "城市": "重庆"},
                       {"绑定电话": "15523261609", "虚拟号码": "18911575971", "线上销售": "陈诗雨", "门店": "解放碑观音桥门店", "城市": "重庆"},
                       {"绑定电话": "15523261538", "虚拟号码": "18580123367", "线上销售": "雷崴", "门店": "解放碑观音桥门店", "城市": "重庆"},
                       {"绑定电话": "15523261528", "虚拟号码": "18580123367", "线上销售": "雷崴", "门店": "解放碑观音桥门店", "城市": "重庆"},
                       {"绑定电话": "15523261507", "虚拟号码": "18580123367", "线上销售": "雷崴", "门店": "解放碑观音桥门店", "城市": "重庆"},
                       {"绑定电话": "15523261501", "虚拟号码": "18580011392", "线上销售": "贺雪", "门店": "解放碑观音桥门店", "城市": "重庆"},
                       {"绑定电话": "15523261376", "虚拟号码": "18580011517", "线上销售": "余清青", "门店": "解放碑观音桥门店", "城市": "重庆"},
                       {"绑定电话": "17088336951", "虚拟号码": "18580011392", "线上销售": "贺雪", "门店": "解放碑观音桥门店", "城市": "重庆"},
                       {"绑定电话": "17150144907", "虚拟号码": "18580011392", "线上销售": "贺雪", "门店": "解放碑观音桥门店", "城市": "重庆"},
                       {"绑定电话": "17098708110", "虚拟号码": "18580011517", "线上销售": "余清青", "门店": "解放碑观音桥门店", "城市": "重庆"},
                       {"绑定电话": "17093800469", "虚拟号码": "18580011517", "线上销售": "余清青", "门店": "解放碑观音桥门店", "城市": "重庆"},
                       {"绑定电话": "18581585914", "虚拟号码": "19950079951", "线上销售": "解浩", "门店": "春熙路门店", "城市": "成都"},
                       {"绑定电话": "18512800114", "虚拟号码": "19950079951", "线上销售": "解浩", "门店": "春熙路门店", "城市": "成都"},
                       {"绑定电话": "17628062646", "虚拟号码": "17301372096", "线上销售": "刘瑶", "门店": "春熙路门店", "城市": "成都"},
                       {"绑定电话": "18581562405", "虚拟号码": "17301372096", "线上销售": "刘瑶", "门店": "春熙路门店", "城市": "成都"},
                       {"绑定电话": "17628064507", "虚拟号码": "17301372096", "线上销售": "刘瑶", "门店": "春熙路门店", "城市": "成都"},
                       {"绑定电话": "18512803418", "虚拟号码": "18611309950", "线上销售": "李雪冬", "门店": "春熙路门店", "城市": "成都"},
                       {"绑定电话": "15682113264", "虚拟号码": "18611309950", "线上销售": "李雪冬", "门店": "春熙路门店", "城市": "成都"},
                       {"绑定电话": "18584874474", "虚拟号码": "18611309950", "线上销售": "李雪冬", "门店": "春熙路门店", "城市": "成都"},
                       {"绑定电话": "13003341487", "虚拟号码": "19910716939", "线上销售": "吕舒舒", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "13003341459", "虚拟号码": "15601024117", "线上销售": "陈志文", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "13003341364", "虚拟号码": "17394775877", "线上销售": "李玉秀", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "13003341354", "虚拟号码": "19910716950", "线上销售": "刘志星", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "13003341346", "虚拟号码": "18911576701", "线上销售": "苏丹丹", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "13003341294", "虚拟号码": "17394775878", "线上销售": "孙文静", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "13003341284", "虚拟号码": "18911578013", "线上销售": "许进", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "13003340934", "虚拟号码": "19910292004", "线上销售": "任鸣悦", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "13003340924", "虚拟号码": "18911570690", "线上销售": "刘青青", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "13003340894", "虚拟号码": "19910716931", "线上销售": "周慧", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "13003340874", "虚拟号码": "17710713571", "线上销售": "周慧", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "13003340854", "虚拟号码": "17394776885", "线上销售": "陈婷", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "13218740013", "虚拟号码": "19910292059", "线上销售": "王文静", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "13218740010", "虚拟号码": "17394775808", "线上销售": "左明星", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "15658323147", "虚拟号码": "17710562763", "线上销售": "屠鑫霞", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "15658325647", "虚拟号码": "15658295268", "线上销售": "高原", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "15658317745", "虚拟号码": "17310751031", "线上销售": "陈加栋", "门店": "杭州分店", "城市": "杭州"},
                       {"绑定电话": "15658318140", "虚拟号码": "18667009181", "线上销售": "于瀛", "门店": "杭州分店", "城市": "杭州"},
                       {"绑定电话": "15658315540", "虚拟号码": "15862380054", "线上销售": "孙炯伟", "门店": "苏州分店", "城市": "苏州"},
                       {"绑定电话": "15658317041", "虚拟号码": "17301382680", "线上销售": "乙华新", "门店": "苏州分店", "城市": "苏州"},
                       {"绑定电话": "15658317845", "虚拟号码": "15862321145", "线上销售": "赵成龙", "门店": "苏州分店", "城市": "苏州"},
                       {"绑定电话": "15658318240", "虚拟号码": "15862379964", "线上销售": "刘靖", "门店": "苏州分店", "城市": "苏州"},
                       {"绑定电话": "15658318548", "虚拟号码": "15151526307", "线上销售": "雷玉欣", "门店": "苏州分店", "城市": "苏州"},
                       {"绑定电话": "15658318848", "虚拟号码": "15862319940", "线上销售": "胥俊", "门店": "苏州分店", "城市": "苏州"},
                       {"绑定电话": "15658319148", "虚拟号码": "15862384355", "线上销售": "陈珺", "门店": "苏州分店", "城市": "苏州"},
                       {"绑定电话": "15658319447", "虚拟号码": "15151526301", "线上销售": "季铭", "门店": "苏州分店", "城市": "苏州"},
                       {"绑定电话": "15658319645", "虚拟号码": "15151526323", "线上销售": "李文俊", "门店": "苏州分店", "城市": "苏州"},
                       {"绑定电话": "17045230463", "虚拟号码": "17310955971", "线上销售": "", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17045230462", "虚拟号码": "17782039451", "线上销售": "", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17098104236", "虚拟号码": "18722387500", "线上销售": "汤倩倩", "门店": "天津鼓楼店", "城市": "天津"},
                       {"绑定电话": "17098253038", "虚拟号码": "17310957014", "线上销售": "", "门店": "北京路门店", "城市": "广州"},
                       {"绑定电话": "13124097684", "虚拟号码": "15338947182", "线上销售": "郭悦", "门店": "三亚湾门店", "城市": "三亚"},
                       {"绑定电话": "17091554315", "虚拟号码": "13476849803", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "17091554294", "虚拟号码": "18086093462", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "17091554195", "虚拟号码": "18086093462", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "17094326826", "虚拟号码": "15887068457", "线上销售": "雷魏林", "门店": "同德广场门店", "城市": "昆明"},
                       {"绑定电话": "15630248860", "虚拟号码": "18722387500", "线上销售": "冉庆", "门店": "天津鼓楼店", "城市": "天津"},
                       {"绑定电话": "17099175835", "虚拟号码": "17782039451", "线上销售": "孔月朋", "门店": "重庆市", "城市": "重庆"},
                       {"绑定电话": "17099175830", "虚拟号码": "15025307379", "线上销售": "欧茗嵩", "门店": "重庆市", "城市": "重庆"},
                       {"绑定电话": "17097760642", "虚拟号码": "18518618553", "线上销售": "章蓓蓓", "门店": "西湖定安路门店", "城市": "杭州"},
                       {"绑定电话": "17099175820", "虚拟号码": "13608816035", "线上销售": "颜瑜", "门店": "同德广场门店", "城市": "昆明"},
                       {"绑定电话": "17097915421", "虚拟号码": "18206216721", "线上销售": "王宏伟", "门店": "观前街门店", "城市": "苏州"},
                       {"绑定电话": "17099175795", "虚拟号码": "15887068457", "线上销售": "雷魏林", "门店": "同德广场门店", "城市": "昆明"},
                       {"绑定电话": "17585460158", "虚拟号码": "13195110158", "线上销售": "孔月朋", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17585460156", "虚拟号码": "13195110158", "线上销售": "孔月朋", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17071521337", "虚拟号码": "16621180242", "线上销售": "", "门店": "迪士尼门店", "城市": "上海"},
                       {"绑定电话": "17099524250", "虚拟号码": "18030187137", "线上销售": "张勇", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "17099523392", "虚拟号码": "18030187137", "线上销售": "张勇", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "17099525622", "虚拟号码": "17392268642", "线上销售": "张勇", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "17585460140", "虚拟号码": "15685133553", "线上销售": "孔月朋", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17585460138", "虚拟号码": "15685133553", "线上销售": "孔月朋", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17585460134", "虚拟号码": "15685133553", "线上销售": "孔月朋", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17082278654", "虚拟号码": "18911579732", "线上销售": "穆璐月", "门店": "天津小白楼店", "城市": "天津"},
                       {"绑定电话": "17092504579", "虚拟号码": "15836139234", "线上销售": "孔月朋", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "17092504578", "虚拟号码": "17749581862", "线上销售": "孔月朋", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "17082278648", "虚拟号码": "15994460524", "线上销售": "张俊伟", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17082278624", "虚拟号码": "18602146335", "线上销售": "谷亮", "门店": "迪士尼门店", "城市": "上海"},
                       {"绑定电话": "17082278614", "虚拟号码": "15994460524", "线上销售": "欧茗嵩", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17082278594", "虚拟号码": "18602146335", "线上销售": "谷亮", "门店": "迪士尼门店", "城市": "上海"},
                       {"绑定电话": "17082278584", "虚拟号码": "18616615214", "线上销售": "欧茗嵩", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17585460132", "虚拟号码": "15685133553", "线上销售": "孔月朋", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17585460128", "虚拟号码": "15685133553", "线上销售": "孔月朋", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17585460126", "虚拟号码": "15685133553", "线上销售": "孔月朋", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17585460125", "虚拟号码": "15685133553", "线上销售": "孔月朋", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17585460124", "虚拟号码": "15685133553", "线上销售": "孔月朋", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17585460118", "虚拟号码": "15685133553", "线上销售": "孔月朋", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17070863404", "虚拟号码": "13269385694", "线上销售": "成宁", "门店": "天津小白楼店", "城市": "天津"},
                       {"绑定电话": "17095054791", "虚拟号码": "15226099827", "线上销售": "袁秋丽", "门店": "观前街门店", "城市": "苏州"},
                       {"绑定电话": "17098257561", "虚拟号码": "18911578270", "线上销售": "庄霖怡", "门店": "北京路门店", "城市": "广州"},
                       {"绑定电话": "18674206457", "虚拟号码": "13237102329", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "17098257551", "虚拟号码": "18911578196", "线上销售": "黄帅", "门店": "天津小白楼店", "城市": "天津"},
                       {"绑定电话": "17093808941", "虚拟号码": "19950241455", "线上销售": "唐婷", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17093808814", "虚拟号码": "15928878045", "线上销售": "汪涛", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17093808754", "虚拟号码": "18581534616", "线上销售": "杨军", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17093808749", "虚拟号码": "15928878045", "线上销售": "汪涛", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17092089462", "虚拟号码": "15360545766", "线上销售": "", "门店": "北京路门店", "城市": "广州"},
                       {"绑定电话": "17093039406", "虚拟号码": "18001165497", "线上销售": "樊宇", "门店": "太原分店", "城市": "太原"},
                       {"绑定电话": "17093039401", "虚拟号码": "17582948969", "线上销售": "胡棋章", "门店": "太原分店", "城市": "太原"},
                       {"绑定电话": "17093039364", "虚拟号码": "17582948969", "线上销售": "胡棋章", "门店": "太原分店", "城市": "太原"},
                       {"绑定电话": "13034983494", "虚拟号码": "18808998237", "线上销售": "吴慧艳", "门店": "三亚分店", "城市": "三亚"},
                       {"绑定电话": "17098714965", "虚拟号码": "15888847241", "线上销售": "章蓓蓓", "门店": "西湖定安路门店", "城市": "杭州"},
                       {"绑定电话": "17092509419", "虚拟号码": "15952008208", "线上销售": "樊宇", "门店": "太原分店", "城市": "太原"},
                       {"绑定电话": "17095054749", "虚拟号码": "18362590980", "线上销售": "孙宇轩", "门店": "苏州观前街店", "城市": "苏州"},
                       {"绑定电话": "17081949134", "虚拟号码": "17582948969", "线上销售": "胡棋章", "门店": "太原分店", "城市": "太原"},
                       {"绑定电话": "17081949124", "虚拟号码": "18649626269", "线上销售": "唐婷", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17093807764", "虚拟号码": "18580632549", "线上销售": "杨军", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17093807742", "虚拟号码": "18580632549", "线上销售": "杨军", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17093807741", "虚拟号码": "17378355783", "线上销售": "杨军", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17093807641", "虚拟号码": "18723015977", "线上销售": "杨军", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17093807640", "虚拟号码": "17782039451", "线上销售": "杨军", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17093807614", "虚拟号码": "15025307379", "线上销售": "谭明钥", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17093807496", "虚拟号码": "19950241455", "线上销售": "唐婷", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17093807494", "虚拟号码": "18581534616", "线上销售": "杨军", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17093807491", "虚拟号码": "19950241455", "线上销售": "唐婷", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17073402764", "虚拟号码": "18030187137", "线上销售": "张勇", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "13047174595", "虚拟号码": "13237102329", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "17073402742", "虚拟号码": "18030187137", "线上销售": "张勇", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "17090414132", "虚拟号码": "18911570792", "线上销售": "廖敏", "门店": "北京路门店", "城市": "广州"},
                       {"绑定电话": "15630252470", "虚拟号码": "18522734831", "线上销售": "", "门店": "天津鼓楼店", "城市": "天津"},
                       {"绑定电话": "17585460107", "虚拟号码": "13195110158", "线上销售": "", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "13096727542", "虚拟号码": "18970273530", "线上销售": "", "门店": "观前街门店", "城市": "苏州"},
                       {"绑定电话": "15676958604", "虚拟号码": "18107796708", "线上销售": "", "门店": "银滩门店", "城市": "北海"},
                       {"绑定电话": "17585460096", "虚拟号码": "13158080973", "线上销售": "", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "13110148470", "虚拟号码": "18523066779", "线上销售": "峥嵘", "门店": "解放碑观音桥门店", "城市": "重庆"},
                       {"绑定电话": "18620443549", "虚拟号码": "18911578270", "线上销售": "庄霖怡", "门店": "北京路门店", "城市": "广州"},
                       {"绑定电话": "18620443253", "虚拟号码": "18911570792", "线上销售": "廖敏", "门店": "北京路门店", "城市": "广州"},
                       {"绑定电话": "17139954732", "虚拟号码": "13076867009", "线上销售": "", "门店": "番禺门店", "城市": "广州"},
                       {"绑定电话": "17123345283", "虚拟号码": "18013116957", "线上销售": "李双才", "门店": "观前街门店", "城市": "苏州"},
                       {"绑定电话": "17123345105", "虚拟号码": "19962110409", "线上销售": "程豫桂", "门店": "观前街门店", "城市": "苏州"},
                       {"绑定电话": "17153645355", "虚拟号码": "19910716993", "线上销售": "侯枫烨/周雯", "门店": "郑州分店", "城市": "郑州"},
                       {"绑定电话": "13020174743", "虚拟号码": "13221371339", "线上销售": "", "门店": "迪士尼门店", "城市": "上海"},
                       {"绑定电话": "13020174742", "虚拟号码": "18616615214", "线上销售": "", "门店": "迪士尼门店", "城市": "上海"},
                       {"绑定电话": "13020174741", "虚拟号码": "13162359115", "线上销售": "", "门店": "迪士尼门店", "城市": "上海"},
                       {"绑定电话": "13124097744", "虚拟号码": "13123397887", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "13250043267", "虚拟号码": "13570602189", "线上销售": "陈静仪", "门店": "吉大门店", "城市": "珠海"},
                       {"绑定电话": "17585460306", "虚拟号码": "15519092740", "线上销售": "", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "13062500324", "虚拟号码": "19850411711", "线上销售": "胡修鹏", "门店": "南京夫子庙店", "城市": "南京"},
                       {"绑定电话": "13062500064", "虚拟号码": "17888105359", "线上销售": "胡修鹏", "门店": "南京夫子庙店", "城市": "南京"},
                       {"绑定电话": "13062541749", "虚拟号码": "13851817197", "线上销售": "夏致远", "门店": "南京夫子庙店", "城市": "南京"},
                       {"绑定电话": "13062559984", "虚拟号码": "15077880594", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "13062559948", "虚拟号码": "17321640051", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "15658018794", "虚拟号码": "13065703075", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "15658018784", "虚拟号码": "18698579776", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "15658018774", "虚拟号码": "18158114182", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "15664701342", "虚拟号码": "17392945202", "线上销售": "胡艳超", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "15527843427", "虚拟号码": "19972084187", "线上销售": "王莹莹", "门店": "中南门店", "城市": "武汉"},
                       {"绑定电话": "15527843407", "虚拟号码": "15623632382", "线上销售": "刘双喜", "门店": "中南门店", "城市": "武汉"},
                       {"绑定电话": "13062559940", "虚拟号码": "17710953275", "线上销售": "孔月朋", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "15534093949", "虚拟号码": "15535391080", "线上销售": "", "门店": "太原分店", "城市": "太原"},
                       {"绑定电话": "15534093947", "虚拟号码": "17331069321", "线上销售": "", "门店": "太原分店", "城市": "太原"},
                       {"绑定电话": "13272639344", "虚拟号码": "18001165497", "线上销售": "", "门店": "太原分店", "城市": "太原"},
                       {"绑定电话": "13272639330", "虚拟号码": "15025307379", "线上销售": "陈小勤", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "13272639312", "虚拟号码": "17582948969", "线上销售": "陈小勤", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "13272639283", "虚拟号码": "18723015977", "线上销售": "冯佳妮", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "13272639268", "虚拟号码": "18723015977", "线上销售": "张悦", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "18620673741", "虚拟号码": "18883167813", "线上销售": "", "门店": "番禺门店", "城市": "广州"},
                       {"绑定电话": "18620672463", "虚拟号码": "15360545766", "线上销售": "蚁创加", "门店": "番禺门店", "城市": "广州"},
                       {"绑定电话": "18620671848", "虚拟号码": "15603070398", "线上销售": "", "门店": "番禺门店", "城市": "广州"},
                       {"绑定电话": "18620671643", "虚拟号码": "18818802079", "线上销售": "", "门店": "番禺门店", "城市": "广州"},
                       {"绑定电话": "18620671540", "虚拟号码": "17688857254", "线上销售": "欧帅", "门店": "番禺门店", "城市": "广州"},
                       {"绑定电话": "18620670487", "虚拟号码": "18594083137", "线上销售": "欧帅", "门店": "番禺门店", "城市": "广州"},
                       {"绑定电话": "17585460290", "虚拟号码": "15585282131", "线上销售": "", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "18620670142", "虚拟号码": "15818812587", "线上销售": "", "门店": "番禺门店", "城市": "广州"},
                       {"绑定电话": "18620669742", "虚拟号码": "17665135626", "线上销售": "", "门店": "番禺门店", "城市": "广州"},
                       {"绑定电话": "18620669740", "虚拟号码": "18680504517", "线上销售": "", "门店": "番禺门店", "城市": "广州"},
                       {"绑定电话": "13062559924", "虚拟号码": "15952008208", "线上销售": "井曼", "门店": "南京夫子庙店", "城市": "南京"},
                       {"绑定电话": "15664701341", "虚拟号码": "17792523150", "线上销售": "陈小勤", "门店": "南京夫子庙店", "城市": "南京"},
                       {"绑定电话": "13272639244", "虚拟号码": "15025307379", "线上销售": "谭明钥", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "13272639228", "虚拟号码": "15025307379", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "13041463693", "虚拟号码": "18013116957", "线上销售": "", "门店": "观前街门店", "城市": "苏州"},
                       {"绑定电话": "15527506420", "虚拟号码": "18602714080", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "15527506417", "虚拟号码": "15623632382", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "15527506407", "虚拟号码": "13476849803", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "13272639187", "虚拟号码": "19923728540", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "13272639185", "虚拟号码": "15086950953", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "13272639173", "虚拟号码": "17830016881", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "13272639168", "虚拟号码": "17815117727", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "13272639161", "虚拟号码": "19922932941", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "13272639152", "虚拟号码": "17378355783", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "13272639151", "虚拟号码": "17320488461", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "13272639140", "虚拟号码": "15025307379", "线上销售": "谭明钥", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "18687021564", "虚拟号码": "18314548643", "线上销售": "", "门店": "同德广场门店", "城市": "昆明"},
                       {"绑定电话": "18687021554", "虚拟号码": "18788100822", "线上销售": "", "门店": "同德广场门店", "城市": "昆明"},
                       {"绑定电话": "18687021549", "虚拟号码": "13187829871", "线上销售": "", "门店": "同德广场门店", "城市": "昆明"},
                       {"绑定电话": "15658018274", "虚拟号码": "15888847241", "线上销售": "童轲雷", "门店": "西湖定安路门店", "城市": "杭州"},
                       {"绑定电话": "15664701248", "虚拟号码": "18392005123", "线上销售": "马新姣", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "15664701247", "虚拟号码": "17392546619", "线上销售": "", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "15664701246", "虚拟号码": "17391820860", "线上销售": "", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "15664701245", "虚拟号码": "17392546625", "线上销售": "", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "15510140320", "虚拟号码": "17301265691", "线上销售": "陈芊", "门店": "天津小白楼店", "城市": "天津"},
                       {"绑定电话": "18687021547", "虚拟号码": "13241803425", "线上销售": "", "门店": "有家民宿世博园3店", "城市": "昆明"},
                       {"绑定电话": "13041463690", "虚拟号码": "18550082108", "线上销售": "严丽娜", "门店": "观前街门店", "城市": "苏州"},
                       {"绑定电话": "13041463689", "虚拟号码": "18362590980", "线上销售": "孙宇轩", "门店": "观前街门店", "城市": "苏州"},
                       {"绑定电话": "13041463687", "虚拟号码": "15720886621", "线上销售": "", "门店": "观前街门店", "城市": "苏州"},
                       {"绑定电话": "13041463686", "虚拟号码": "19951006003", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "15510541205", "虚拟号码": "17772773202", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "15510541120", "虚拟号码": "18322696056", "线上销售": "张旭拍", "门店": "天津鼓楼店", "城市": "天津"},
                       {"绑定电话": "15510541107", "虚拟号码": "15822123108", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "17045230464", "虚拟号码": "18030187137", "线上销售": "张勇", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "17585460093", "虚拟号码": "18585030054", "线上销售": "", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17585460087", "虚拟号码": "17557221210", "线上销售": "", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17585460086", "虚拟号码": "15519081285", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "17585460083", "虚拟号码": "18585033701", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "13221371317", "虚拟号码": "19962110409", "线上销售": "", "门店": "观前街门店", "城市": "苏州"},
                       {"绑定电话": "15534093646", "虚拟号码": "18001165497", "线上销售": "樊宇", "门店": "太原分店", "城市": "太原"},
                       {"绑定电话": "15534093644", "虚拟号码": "15666680755", "线上销售": "", "门店": "太原分店", "城市": "太原"},
                       {"绑定电话": "15534093642", "虚拟号码": "19935115767", "线上销售": "", "门店": "", "城市": ""},
                       {"绑定电话": "17181013680", "虚拟号码": "17392268642", "线上销售": "贺裕华", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "17181012607", "虚拟号码": "18602146335", "线上销售": "谷亮", "门店": "迪士尼门店", "城市": "上海"},
                       {"绑定电话": "17096571965", "虚拟号码": "15887068457", "线上销售": "", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17585460071", "虚拟号码": "15685133553", "线上销售": "孔月朋", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17585460069", "虚拟号码": "15685133553", "线上销售": "孔月朋", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17138378303", "虚拟号码": "15928878045", "线上销售": "汪涛", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17138378292", "虚拟号码": "19950241455", "线上销售": "唐婷", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17181012175", "虚拟号码": "18030187137", "线上销售": "陈小勤", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "17188388959", "虚拟号码": "19950241455", "线上销售": "唐婷", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17188387781", "虚拟号码": "15928878045", "线上销售": "汪涛", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17188385285", "虚拟号码": "15928878045", "线上销售": "汪涛", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17188385381", "虚拟号码": "15928878045", "线上销售": "汪涛", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "13063524943", "虚拟号码": "17749581862", "线上销售": "孔月朋", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "17188388538", "虚拟号码": "19950241455", "线上销售": "唐婷", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17088336857", "虚拟号码": "15025307379", "线上销售": "谭明钥", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17088336851", "虚拟号码": "15025307379", "线上销售": "谭明钥", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17088336926", "虚拟号码": "17830082881", "线上销售": "欧茗嵩", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17096572504", "虚拟号码": "13608816035", "线上销售": "", "门店": "有家民宿北辰店", "城市": "昆明"},
                       {"绑定电话": "17096572498", "虚拟号码": "13608816035", "线上销售": "", "门店": "有家民宿梁家河店", "城市": "昆明"},
                       {"绑定电话": "13143436634", "虚拟号码": "15601024640", "线上销售": "高杉", "门店": "天津小白楼店", "城市": "天津"},
                       {"绑定电话": "17095637542", "虚拟号码": "18602146335", "线上销售": "谷亮", "门店": "迪士尼门店", "城市": "上海"},
                       {"绑定电话": "17096572486", "虚拟号码": "15887068457", "线上销售": "", "门店": "同德广场门店", "城市": "昆明"},
                       {"绑定电话": "17150871260", "虚拟号码": "18594083137", "线上销售": "欧帅", "门店": "番禺门店", "城市": "广州"},
                       {"绑定电话": "17093039220", "虚拟号码": "17582948969", "线上销售": "李振兴", "门店": "太原特殊门店", "城市": "太原"},
                       {"绑定电话": "17088336967", "虚拟号码": "18581534616", "线上销售": "杨军", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17095637469", "虚拟号码": "15994460524", "线上销售": "", "门店": "迪士尼门店", "城市": "上海"},
                       {"绑定电话": "17095637465", "虚拟号码": "15994460524", "线上销售": "", "门店": "迪士尼门店", "城市": "上海"},
                       {"绑定电话": "13143436439", "虚拟号码": "18594083137", "线上销售": "欧帅", "门店": "番禺门店", "城市": "广州"},
                       {"绑定电话": "15534093594", "虚拟号码": "17582948969", "线上销售": "胡棋章", "门店": "太原分店", "城市": "太原"},
                       {"绑定电话": "17093039358", "虚拟号码": "18001165497", "线上销售": "李振兴", "门店": "太原分店", "城市": "太原"},
                       {"绑定电话": "17096576770", "虚拟号码": "13608816035", "线上销售": "", "门店": "有家民宿东风广场店", "城市": "昆明"},
                       {"绑定电话": "13124796818", "虚拟号码": "18920962550", "线上销售": "刘春燕", "门店": "天津小白楼店", "城市": "天津"},
                       {"绑定电话": "13179714393", "虚拟号码": "17710953275", "线上销售": "王迎", "门店": "南京新街口店", "城市": "南京"},
                       {"绑定电话": "17076246755", "虚拟号码": "18653895290", "线上销售": "", "门店": "有家民宿泰安岱岳店", "城市": "泰安"},
                       {"绑定电话": "17186408026", "虚拟号码": "15862442306", "线上销售": "常晓旭", "门店": "苏州观前街店", "城市": "苏州"},
                       {"绑定电话": "17186401606", "虚拟号码": "18913500171", "线上销售": "汤磊", "门店": "苏州观前街店", "城市": "苏州"},
                       {"绑定电话": "17098255856", "虚拟号码": "17710563182", "线上销售": "杨晓花", "门店": "天津小白楼店", "城市": "天津"},
                       {"绑定电话": "13097917394", "虚拟号码": "17310748364", "线上销售": "", "门店": "有家民宿北海银海区店", "城市": "北海"},
                       {"绑定电话": "13014832514", "虚拟号码": "15004643614", "线上销售": "", "门店": "有家民宿北海海城区店", "城市": "北海"},
                       {"绑定电话": "17093038048", "虚拟号码": "18001165497", "线上销售": "李振兴", "门店": "太原分店", "城市": "太原"},
                       {"绑定电话": "17093030648", "虚拟号码": "17582948969", "线上销售": "李振兴", "门店": "太原分店", "城市": "太原"},
                       {"绑定电话": "13124796851", "虚拟号码": "18030187137", "线上销售": "陈小勤", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "13124796859", "虚拟号码": "17392268642", "线上销售": "冯佳妮", "门店": "西安分店", "城市": "西安"},
                       {"绑定电话": "18620487385", "虚拟号码": "15360545766", "线上销售": "蚁创加", "门店": "广州有家民宿番禺长隆店", "城市": "广州"},
                       {"绑定电话": "18620487636", "虚拟号码": "18883167813", "线上销售": "", "门店": "广州有家民宿北京路店", "城市": "广州"},
                       {"绑定电话": "13017410904", "虚拟号码": "13195110158", "线上销售": "", "门店": "贵阳分店", "城市": "贵阳"},
                       {"绑定电话": "17187188724", "虚拟号码": "18672341267", "线上销售": "陈顺明", "门店": "中南门店", "城市": "武汉"},
                       {"绑定电话": "17150194423", "虚拟号码": "18723015977", "线上销售": "张悦", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17150148432", "虚拟号码": "17320488461", "线上销售": "程垦", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17150149048", "虚拟号码": "17830082881", "线上销售": "欧茗嵩", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17081940198", "虚拟号码": "17310957014", "线上销售": "", "门店": "厦门分店", "城市": "厦门"},
                       {"绑定电话": "17098049611", "虚拟号码": "15888847241", "线上销售": "童轲雷", "门店": "有家民宿西湖定安路1店", "城市": "杭州"},
                       {"绑定电话": "17196632214", "虚拟号码": "18518618553", "线上销售": "童轲雷", "门店": "有家民宿西湖定安路2店", "城市": "杭州"},
                       {"绑定电话": "17187784267", "虚拟号码": "15994460524", "线上销售": "张俊伟", "门店": "迪士尼门店", "城市": "上海"},
                       {"绑定电话": "17187784327", "虚拟号码": "15994460524", "线上销售": "张俊伟", "门店": "迪士尼门店", "城市": "上海"},
                       {"绑定电话": "17187480654", "虚拟号码": "15994460524", "线上销售": "张俊伟", "门店": "迪士尼门店", "城市": "上海"},
                       {"绑定电话": "17150194219", "虚拟号码": "18723015977", "线上销售": "张悦", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17150194167", "虚拟号码": "18723015977", "线上销售": "张悦", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17150192134", "虚拟号码": "15025307379", "线上销售": "谭明钥", "门店": "南坪店", "城市": "重庆"},
                       {"绑定电话": "17150149946", "虚拟号码": "18581534616", "线上销售": "杨军", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17150190641", "虚拟号码": "18581534616", "线上销售": "杨军", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17150197934", "虚拟号码": "18581534616", "线上销售": "杨军", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17150147244", "虚拟号码": "18581534616", "线上销售": "杨军", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17150148414", "虚拟号码": "15928878045", "线上销售": "汪涛", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17150144909", "虚拟号码": "19950241455", "线上销售": "唐婷", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17150149324", "虚拟号码": "18581534616", "线上销售": "杨军", "门店": "宽窄巷子店", "城市": "成都"},
                       {"绑定电话": "17081833435", "虚拟号码": "15651032917", "线上销售": "章振宇", "门店": "南京夫子庙店", "城市": "南京"},
                       {"绑定电话": "17092504582", "虚拟号码": "13851817197", "线上销售": "夏致远", "门店": "南京夫子庙店", "城市": "南京"},
                       {"绑定电话": "17092509420", "虚拟号码": "15952008208", "线上销售": "井曼", "门店": "南京夫子庙店", "城市": "南京"},
                       {"绑定电话": "13016420805", "虚拟号码": "18670066841", "线上销售": "刘捷", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "13016420803", "虚拟号码": "19910716989", "线上销售": "周瀛环", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "13016420802", "虚拟号码": "19910716961", "线上销售": "姚依", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "13016420801", "虚拟号码": "19910716937", "线上销售": "刘雅桸", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "13016420799", "虚拟号码": "18670741154", "线上销售": "杨清宇", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "17090417483", "虚拟号码": "17090417483", "线上销售": "邓溪", "门店": "太平街店", "城市": "长沙"},
                       {"绑定电话": "15658321148", "虚拟号码": "18515945631", "线上销售": "张泽南", "门店": "上海分店", "城市": "上海"},
                       {"绑定电话": "15658318170", "虚拟号码": "18911571280", "线上销售": "于瀛", "门店": "杭州分店", "城市": "杭州"},
                       {"绑定电话": "15658317745", "虚拟号码": "17310751031", "线上销售": "陈加栋", "门店": "杭州分店", "城市": "杭州"},
                       {"绑定电话": "15658325647", "虚拟号码": "15658295268", "线上销售": "高原", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "15658318043", "虚拟号码": "15658295268", "线上销售": "高原", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "15658317640", "虚拟号码": "15658295268", "线上销售": "高原", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "15658323147", "虚拟号码": "17710562763", "线上销售": "屠鑫霞", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "15658316049", "虚拟号码": "17710562763", "线上销售": "屠鑫霞", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "15658315140", "虚拟号码": "17710562763", "线上销售": "屠鑫霞", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "15658327846", "虚拟号码": "13216827569", "线上销售": "金颖", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "15658329149", "虚拟号码": "13216827569", "线上销售": "金颖", "门店": "宁波分店", "城市": "宁波"},
                       {"绑定电话": "17196632214", "虚拟号码": "18518618553", "线上销售": "章蓓蓓", "门店": "西湖定安路门店", "城市": "杭州"},
                       {"绑定电话": "15658018274", "虚拟号码": "15888847241", "线上销售": "章蓓蓓", "门店": "西湖定安路门店", "城市": "杭州"},
                       {"绑定电话": "17098049611", "虚拟号码": "15888847241", "线上销售": "章蓓蓓", "门店": "西湖定安路门店", "城市": "杭州"},
                       {"绑定电话": "17098257549", "虚拟号码": "13907603620", "线上销售": "黄玉妙", "门店": "三亚分店", "城市": "三亚"}]

    return pd.DataFrame(phone_city_list)


def phone_sale_day(st=None, et=None):
    """
    一周的 电话接起率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = phone(st, et)
    df_g = df.groupby(['绑定电话']).agg({'来电次数': 'sum', '接起电话次数': 'sum'})

    # df_g['电话接起率'] = round(df_g['接起电话次数'] / df_g['来电次数'] * 100, 2)
    df_g['电话接起率'] = df_g.apply(lambda row: percentage(row, '接起电话次数', '来电次数'), axis=1)
    # df_g['电话接起率'] = str(df_g['电话接起率']) + "%"

    df_g = df_g.reset_index()
    phone_city_df = phone_city()

    df_g = pd.merge(phone_city_df, df_g, how='left')
    df_g = df_util.df_set_index(df_g, ['城市', '绑定电话'])

    df_g = df_util.df_set_first_title(df_g, "日")
    return df_g


def phone_num_rate(st=None, et=None):
    """
    一周的 电话接起率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = phone(st, et)
    df_g = df.groupby(['绑定电话']).agg({'来电次数': 'sum', '接起电话次数': 'sum'})

    df_g['电话接起率'] = df_g.apply(lambda row: percentage(row, '接起电话次数', '来电次数'), axis=1)

    return df_g


def phone_sale_week(st=None, et=None):
    """
    一周的 电话接起率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = phone(st, et)
    df_g = df.groupby(['绑定电话']).agg({'来电次数': 'sum', '接起电话次数': 'sum'})

    # df_g['电话接起率'] = round(df_g['接起电话次数'] / df_g['来电次数'] * 100, 2)
    df_g['电话接起率'] = df_g.apply(lambda row: percentage(row, '接起电话次数', '来电次数'), axis=1)
    # df_g['电话接起率'] = str(df_g['电话接起率']) + "%"

    df_g = df_g.reset_index()
    phone_city_df = phone_city()
    df_g = pd.merge(phone_city_df, df_g, how='left')
    df_g = df_util.df_set_index(df_g, ['城市', '绑定电话'])

    df_g = df_util.df_set_first_title(df_g, "周")
    return df_g


def phone_sale_month(st=None, et=None):
    """
    一月的 电话接起率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = phone(st, et)
    # '来电人次': 'sum', '接起电话人次': 'sum'
    df_g = df.groupby(['绑定电话']).agg({'来电次数': 'sum', '接起电话次数': 'sum'})

    # df_g['电话接起率'] = round(df_g['接起电话次数'] / df_g['来电次数'] * 100, 2)
    df_g['电话接起率'] = df_g.apply(lambda row: percentage(row, '接起电话次数', '来电次数'), axis=1)

    df_g = df_g.reset_index()
    phone_city_df = phone_city()
    df_g = pd.merge(phone_city_df, df_g, how='left')
    df_g = df_util.df_set_index(df_g, ['城市', '绑定电话'])

    # df_g['电话接起率'] = str(df_g['电话接起率']) + "%"
    df_g = df_util.df_set_first_title(df_g, "月")
    return df_g


def phone_work_day(st=None, et=None):
    """
    日电话接起率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = phone(st, et)
    df_g = df.groupby(['绑定电话']).agg({'来电次数': 'sum', '接起电话次数': 'sum'})

    # df_g['电话接起率'] = round(df_g['接起电话次数'] / df_g['来电次数'] * 100, 2)
    df_g['电话接起率'] = df_g.apply(lambda row: percentage(row, '接起电话次数', '来电次数'), axis=1)
    df_g = df_util.df_set_first_title(df_g, "日")

    df_g = df_g.reset_index()
    phone_city_df = phone_city()
    df_g = pd.merge(phone_city_df, df_g, how='left')
    df_g = df_util.df_set_index(df_g, ['城市', '绑定电话'])

    return df_g


def phone_work_week(st=None, et=None):
    """
    一周的 电话接起率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = phone(st, et)
    df_g = df.groupby(['绑定电话']).agg({'来电次数': 'sum', '接起电话次数': 'sum'})

    # df_g['电话接起率'] = round(df_g['接起电话次数'] / df_g['来电次数'] * 100, 2)
    df_g['电话接起率'] = df_g.apply(lambda row: percentage(row, '接起电话次数', '来电次数'), axis=1)
    # df_g['电话接起率'] = str(df_g['电话接起率']) + "%"

    df_g = df_g.reset_index()
    phone_city_df = phone_city()
    df_g = pd.merge(phone_city_df, df_g, how='left')
    df_g = df_util.df_set_index(df_g, ['城市', '绑定电话'])

    df_g = df_util.df_set_first_title(df_g, "周")

    return df_g


def phone_work_month(st=None, et=None):
    """
    一月的 电话接起率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = phone(st, et)

    # df_g = df.groupby(['城市', '门店']).agg({'来电次数': 'sum', '接起电话次数': 'sum'})
    df_g = df.groupby(['绑定电话']).agg({'来电次数': 'sum', '接起电话次数': 'sum'})

    # df_g['电话接起率'] = round(df_g['接起电话次数'] / df_g['来电次数'] * 100, 2)
    df_g['电话接起率'] = df_g.apply(lambda row: percentage(row, '接起电话次数', '来电次数'), axis=1)

    df_g = df_g.reset_index()
    phone_city_df = phone_city()
    df_g = pd.merge(phone_city_df, df_g, how='left')
    df_g = df_util.df_set_index(df_g, ['城市', '绑定电话'])

    # df_g['电话接起率'] = str(df_g['电话接起率']) + "%"
    df_g = df_util.df_set_first_title(df_g, "月")
    return df_g


def phone_city_day(st=None, et=None):
    """
    日电话接起率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = phone(st, et)
    phone_city_df = phone_city()
    df_g = pd.merge(phone_city_df, df, how='left')
    df_g = df_g.groupby(['城市']).agg({'来电次数': 'sum', '接起电话次数': 'sum'})
    df_g['电话接起率'] = df_g.apply(lambda row: percentage(row, '接起电话次数', '来电次数'), axis=1)
    df_g['公司'] = '有家'
    df_g = df_g.reset_index()
    return df_g


def phone_city_week(st=None, et=None):
    """
    一周的 电话接起率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = phone(st, et)
    phone_city_df = phone_city()
    df_g = pd.merge(phone_city_df, df, how='left')
    df_g = df_g.groupby(['城市']).agg({'来电次数': 'sum', '接起电话次数': 'sum'})
    df_g['电话接起率'] = df_g.apply(lambda row: percentage(row, '接起电话次数', '来电次数'), axis=1)
    df_g = df_g.reset_index()
    df_g['公司'] = '有家'
    return df_g


def phone_city_month(st=None, et=None):
    """
    一月的 电话接起率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = phone(st, et)
    phone_city_df = phone_city()
    df_g = pd.merge(phone_city_df, df, how='left')
    df_g = df_g.groupby(['城市']).agg({'来电次数': 'sum', '接起电话次数': 'sum'})
    df_g['电话接起率'] = df_g.apply(lambda row: percentage(row, '接起电话次数', '来电次数'), axis=1)
    df_g = df_g.reset_index()
    df_g['公司'] = '有家'
    return df_g


def phone_detail_week(st=None, et=None):
    """
    一周的 电话接起详情
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("电话接起详情 st={st}".format(st=st))
    log.info("电话接起详情 et={et}".format(et=et))
    df_g = phone_detail(st, et)

    phone_city_df = phone_city()
    df_g = pd.merge(phone_city_df, df_g, how='left')
    df_g = df_util.df_set_index(df_g, ['城市', '绑定电话'])

    return df_g


def phone_detail_day(st=None, et=None):
    """
    一周的 电话接起详情
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("电话接起详情 st={st}".format(st=st))
    log.info("电话接起详情 et={et}".format(et=et))
    df_g = phone_detail(st, et)

    phone_city_df = phone_city()
    df_g = pd.merge(phone_city_df, df_g, how='left')
    df_g = df_util.df_set_index(df_g, ['城市', '绑定电话'])

    return df_g


def phone_detail_month(st=None, et=None):
    """
    一月的 电话接起详情
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起详情 st={st}".format(st=st))
    log.info("电话接起详情 et={et}".format(et=et))
    df_g = phone_detail(st, et)

    phone_city_df = phone_city()
    df_g = pd.merge(phone_city_df, df_g, how='left')
    df_g = df_util.df_set_index(df_g, ['城市', '绑定电话'])

    return df_g


def im_sale(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    销售维度区分公司
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:
    """
    df = im(st, et, own_type, channel_ids)
    df_g = df.groupby(['公司', '城市', '门店', '门店销售', '渠道']).agg({'咨询数': 'sum', '回复数': 'sum',
                                                             '五分钟回复数': 'sum', '完整回复数': 'sum'})

    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
    # df_g['im咨询五分钟回复率'] = round(df_g['五分钟回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
    # df_g['im咨询完整回复率'] = round(df_g['完整回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)
    return df_g


def im_sale_day(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df_g = im_sale(st=st, et=et, own_type=own_type, channel_ids=channel_ids)

    # df_g = df_g.reset_index()
    df_g = df_util.df_set_first_title(df_g, "日")

    return df_g


def im_sale_week(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df_g = im_sale(st=st, et=et, own_type=own_type, channel_ids=channel_ids)
    df_g = df_util.df_set_first_title(df_g, "周")

    return df_g


def im_sale_month(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    一月的 电话接起率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df_g = im_sale(st=st, et=et, own_type=own_type, channel_ids=channel_ids)
    df_g = df_util.df_set_first_title(df_g, "月")
    return df_g


def im_online_sale_data_no_company(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    销售 im 回复率问题 不区分公司
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:
    """
    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st=st, et=et, own_type=own_type, channel_ids=channel_ids)
    df_g = df.groupby(['门店销售']).agg({'咨询数': 'sum', '回复数': 'sum', '五分钟回复数': 'sum', '完整回复数': 'sum'})

    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)
    df_g = df_util.df_reset_index(df_g)
    return df_g


def im_offline_sale_data_no_company(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    销售 im 回复率问题 不区分公司
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:
    """
    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st=st, et=et, own_type=own_type, channel_ids=channel_ids)
    df_g = df.groupby(['门店管家']).agg({'咨询数': 'sum', '回复数': 'sum', '五分钟回复数': 'sum', '完整回复数': 'sum'})

    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)
    df_g = df_util.df_reset_index(df_g)
    return df_g


def im_work_day(st=None, et=None, has_first=True, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param has_first:
    :param own_type:
    :param channel_ids:
    :return:

    A.work_name 门店,
    A.realname 门店销售,
    concat(A.lodge_id,"") 房源id,
    A.short_name 城市,
    A.name 渠道,
    A.咨询数,
    A.回复数,
    sum(if(timestampdiff(second, A.最早回复时间, A.最早咨询时间) <= 5*60,A.回复数,0)) 五分钟回复数,
    sum(if(A.最晚回复时间>=A.最晚咨询时间 ,A.回复数,0)) 完整回复数
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st, et, own_type, channel_ids=channel_ids)
    df_g = df.groupby(['城市', '门店']).agg({'咨询数': 'sum', '回复数': 'sum',
                                         '五分钟回复数': 'sum', '完整回复数': 'sum'})

    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
    # df_g['im咨询五分钟回复率'] = round(df_g['五分钟回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
    # df_g['im咨询完整回复率'] = round(df_g['完整回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)

    # df_g = df_g.reset_index()
    if has_first:
        df_g = df_util.df_set_first_title(df_g, "日")
    else:
        df_g = df_g.reset_index()

    return df_g


def im_work_week(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    A.work_name 门店,
    A.realname 门店销售,
    concat(A.lodge_id,"") 房源id,
    A.short_name 城市,
    A.name 渠道,
    A.咨询数,
    A.回复数,
    sum(if(timestampdiff(second, A.最早回复时间, A.最早咨询时间) <= 5*60,A.回复数,0)) 五分钟回复数,
    sum(if(A.最晚回复时间>=A.最晚咨询时间 ,A.回复数,0)) 完整回复数
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st, et, own_type)
    df_g = df.groupby(['城市', '门店']).agg({'咨询数': 'sum', '回复数': 'sum',
                                         '五分钟回复数': 'sum', '完整回复数': 'sum'})

    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
    # df_g['im咨询五分钟回复率'] = round(df_g['五分钟回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
    # df_g['im咨询完整回复率'] = round(df_g['完整回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)

    # df_g = df_g.reset_index()
    df_g = df_util.df_set_first_title(df_g, "周")

    return df_g


def im_work_month(st=None, et=None, own_type=(1, 2, 3)):
    """
    一月的 电话接起率
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = im(st, et, own_type)
    df_g = df.groupby(['城市', '门店']).agg({'咨询数': 'sum', '回复数': 'sum',
                                         '五分钟回复数': 'sum', '完整回复数': 'sum'})

    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
    # df_g['im咨询五分钟回复率'] = round(df_g['五分钟回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
    # df_g['im咨询完整回复率'] = round(df_g['完整回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)
    # df_g = df_g.reset_index()
    df_g = df_util.df_set_first_title(df_g, "月")
    return df_g


def im_service_day(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    A.work_name 门店,
    A.realname 门店销售,
    concat(A.lodge_id,"") 房源id,
    A.short_name 城市,
    A.name 渠道,
    A.咨询数,
    A.回复数,
    sum(if(timestampdiff(second, A.最早回复时间, A.最早咨询时间) <= 5*60,A.回复数,0)) 五分钟回复数,
    sum(if(A.最晚回复时间>=A.最晚咨询时间 ,A.回复数,0)) 完整回复数
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st, et, own_type)
    try:
        df_g = df.groupby(['公司', '城市', '服务中心']).agg({'咨询数': 'sum', '回复数': 'sum',
                                                     '五分钟回复数': 'sum', '完整回复数': 'sum'})

        df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
        # df_g['im咨询五分钟回复率'] = round(df_g['五分钟回复数'] / df_g['咨询数'] * 100, 2)
        df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
        # df_g['im咨询完整回复率'] = round(df_g['完整回复数'] / df_g['咨询数'] * 100, 2)
        df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)

        df_g = df_g.reset_index()
        # df_g = df_util.df_set_first_title(df_g, "日")
    except:
        x = [{'公司': '', '城市': '', '服务中心': '', 'im咨询回复率': '', 'im咨询五分钟回复率': '', 'im咨询完整回复率': ''}]
        return pd.DataFrame(x)

    return df_g[['公司', '城市', '服务中心', 'im咨询回复率', 'im咨询五分钟回复率', 'im咨询完整回复率']]


def im_service_week(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    A.work_name 门店,
    A.realname 门店销售,
    concat(A.lodge_id,"") 房源id,
    A.short_name 城市,
    A.name 渠道,
    A.咨询数,
    A.回复数,
    sum(if(timestampdiff(second, A.最早回复时间, A.最早咨询时间) <= 5*60,A.回复数,0)) 五分钟回复数,
    sum(if(A.最晚回复时间>=A.最晚咨询时间 ,A.回复数,0)) 完整回复数
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st, et, own_type)
    try:
        df_g = df.groupby(['公司', '城市', '服务中心']).agg({'咨询数': 'sum', '回复数': 'sum',
                                                     '五分钟回复数': 'sum', '完整回复数': 'sum'})

        df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
        # df_g['im咨询五分钟回复率'] = round(df_g['五分钟回复数'] / df_g['咨询数'] * 100, 2)
        df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
        # df_g['im咨询完整回复率'] = round(df_g['完整回复数'] / df_g['咨询数'] * 100, 2)
        df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)

        df_g = df_g.reset_index()
        # df_g = df_util.df_set_first_title(df_g, "周")
    except:
        x = [{'公司': '', '城市': '', '服务中心': '', 'im咨询回复率': '', 'im咨询五分钟回复率': '', 'im咨询完整回复率': ''}]
        df_g = pd.DataFrame(x)

    return df_g[['公司', '城市', '服务中心', 'im咨询回复率', 'im咨询五分钟回复率', 'im咨询完整回复率']]


def im_service_month(st=None, et=None, own_type=(1, 2, 3)):
    """
    一月的 电话接起率
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = im(st, et, own_type)
    try:
        df_g = df.groupby(['公司', '城市', '服务中心']).agg({'咨询数': 'sum', '回复数': 'sum',
                                                     '五分钟回复数': 'sum', '完整回复数': 'sum'})

        df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
        # df_g['im咨询五分钟回复率'] = round(df_g['五分钟回复数'] / df_g['咨询数'] * 100, 2)
        df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
        # df_g['im咨询完整回复率'] = round(df_g['完整回复数'] / df_g['咨询数'] * 100, 2)
        df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)
        df_g = df_g.reset_index()
        # df_g = df_util.df_set_first_title(df_g, "月")
    except:
        x = [{'公司': '', '城市': '', '服务中心': '', 'im咨询回复率': '', 'im咨询五分钟回复率': '', 'im咨询完整回复率': ''}]
        df_g = pd.DataFrame(x)
    return df_g[['公司', '城市', '服务中心', 'im咨询回复率', 'im咨询五分钟回复率', 'im咨询完整回复率']]


def im_city_day(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:

    A.work_name 门店,
    A.realname 门店销售,
    concat(A.lodge_id,"") 房源id,
    A.short_name 城市,
    A.name 渠道,
    A.咨询数,
    A.回复数,
    sum(if(timestampdiff(second, A.最早回复时间, A.最早咨询时间) <= 5*60,A.回复数,0)) 五分钟回复数,
    sum(if(A.最晚回复时间>=A.最晚咨询时间 ,A.回复数,0)) 完整回复数
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st, et, own_type, channel_ids)
    # return df
    try:
        df_g = df.groupby(['公司', '城市']).agg({'咨询数': 'sum', '回复数': 'sum', '五分钟回复数': 'sum', '完整回复数': 'sum'})

        df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
        df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
        df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)

        df_g = df_g.reset_index()
    except:
        x = [{'公司': '', '城市': '', 'im咨询回复率': '', 'im咨询五分钟回复率': '', 'im咨询完整回复率': ''}]
        return pd.DataFrame(x)
    return df_g[['公司', '城市', 'im咨询回复率', 'im咨询五分钟回复率', 'im咨询完整回复率']]


def im_city(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:

    A.work_name 门店,
    A.realname 门店销售,
    concat(A.lodge_id,"") 房源id,
    A.short_name 城市,
    A.name 渠道,
    A.咨询数,
    A.回复数,
    sum(if(timestampdiff(second, A.最早回复时间, A.最早咨询时间) <= 5*60,A.回复数,0)) 五分钟回复数,
    sum(if(A.最晚回复时间>=A.最晚咨询时间 ,A.回复数,0)) 完整回复数
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st, et, own_type, channel_ids)
    # return df
    df_g = df.groupby(['公司', '城市']).agg({'咨询数': 'sum', '回复数': 'sum', '五分钟回复数': 'sum', '完整回复数': 'sum'})

    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)

    df_g = df_g.reset_index()
    return df_g


def im_district(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:

    A.work_name 门店,
    A.realname 门店销售,
    concat(A.lodge_id,"") 房源id,
    A.short_name 城市,
    A.name 渠道,
    A.咨询数,
    A.回复数,
    sum(if(timestampdiff(second, A.最早回复时间, A.最早咨询时间) <= 5*60,A.回复数,0)) 五分钟回复数,
    sum(if(A.最晚回复时间>=A.最晚咨询时间 ,A.回复数,0)) 完整回复数
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st, et, own_type, channel_ids)
    # return df
    df_g = df.groupby(['大区']).agg({'咨询数': 'sum', '回复数': 'sum', '五分钟回复数': 'sum', '完整回复数': 'sum'})

    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)

    df_g = df_g.reset_index()
    return df_g


def im_city_week(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    A.work_name 门店,
    A.realname 门店销售,
    concat(A.lodge_id,"") 房源id,
    A.short_name 城市,
    A.name 渠道,
    A.咨询数,
    A.回复数,
    sum(if(timestampdiff(second, A.最早回复时间, A.最早咨询时间) <= 5*60,A.回复数,0)) 五分钟回复数,
    sum(if(A.最晚回复时间>=A.最晚咨询时间 ,A.回复数,0)) 完整回复数
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st, et, own_type)
    try:
        df_g = df.groupby(['公司', '城市']).agg({'咨询数': 'sum', '回复数': 'sum', '五分钟回复数': 'sum', '完整回复数': 'sum'})

        df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
        df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
        df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)

        df_g = df_g.reset_index()
    except:
        x = [{'公司': '', '城市': '', 'im咨询回复率': '', 'im咨询五分钟回复率': '', 'im咨询完整回复率': ''}]
        df_g = pd.DataFrame(x)

    return df_g[['公司', '城市', 'im咨询回复率', 'im咨询五分钟回复率', 'im咨询完整回复率']]


def im_city_month(st=None, et=None, own_type=(1, 2, 3)):
    """
    一月的 电话接起率
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = im(st, et, own_type)
    try:
        df_g = df.groupby(['公司', '城市']).agg({'咨询数': 'sum', '回复数': 'sum', '五分钟回复数': 'sum', '完整回复数': 'sum'})

        df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
        df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
        df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)
        df_g = df_g.reset_index()
    except:
        x = [{'公司': '', '城市': '', 'im咨询回复率': '', 'im咨询五分钟回复率': '', 'im咨询完整回复率': ''}]
        df_g = pd.DataFrame(x)
    return df_g[['公司', '城市', 'im咨询回复率', 'im咨询五分钟回复率', 'im咨询完整回复率']]


def im_online_day(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    A.work_name 门店,
    A.realname 门店销售,
    concat(A.lodge_id,"") 房源id,
    A.short_name 城市,
    A.name 渠道,
    A.咨询数,
    A.回复数,
    sum(if(timestampdiff(second, A.最早回复时间, A.最早咨询时间) <= 5*60,A.回复数,0)) 五分钟回复数,
    sum(if(A.最晚回复时间>=A.最晚咨询时间 ,A.回复数,0)) 完整回复数
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st, et, own_type)

    df_g = df.groupby(["公司", "城市", "门店", "门店销售"]).agg({'咨询数': 'sum', '回复数': 'sum', '五分钟回复数': 'sum', '完整回复数': 'sum'})

    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)

    df_g = df_g.reset_index()

    return df_g[["公司", "城市", "门店", "门店销售", 'im咨询回复率', 'im咨询五分钟回复率', 'im咨询完整回复率']]


def im_online_week(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    A.work_name 门店,
    A.realname 门店销售,
    concat(A.lodge_id,"") 房源id,
    A.short_name 城市,
    A.name 渠道,
    A.咨询数,
    A.回复数,
    sum(if(timestampdiff(second, A.最早回复时间, A.最早咨询时间) <= 5*60,A.回复数,0)) 五分钟回复数,
    sum(if(A.最晚回复时间>=A.最晚咨询时间 ,A.回复数,0)) 完整回复数
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st, et, own_type)
    df_g = df.groupby(['公司', '门店销售']).agg({'咨询数': 'sum', '回复数': 'sum', '五分钟回复数': 'sum', '完整回复数': 'sum'})

    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)

    df_g = df_g.reset_index()

    return df_g[['公司', '门店销售', 'im咨询回复率', 'im咨询五分钟回复率', 'im咨询完整回复率']]


def im_online_month(st=None, et=None, own_type=(1, 2, 3)):
    """
    一月的 电话接起率
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = im(st, et, own_type)
    df_g = df.groupby(['公司', '门店销售']).agg({'咨询数': 'sum', '回复数': 'sum', '五分钟回复数': 'sum', '完整回复数': 'sum'})

    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)
    df_g = df_g.reset_index()
    return df_g[['公司', '门店销售', 'im咨询回复率', 'im咨询五分钟回复率', 'im咨询完整回复率']]


def im_lodge_day(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    A.work_name 门店,
    A.realname 门店销售,
    concat(A.lodge_id,"") 房源id,
    A.short_name 城市,
    A.name 渠道,
    A.咨询数,
    A.回复数,
    sum(if(timestampdiff(second, A.最早回复时间, A.最早咨询时间) <= 5*60,A.回复数,0)) 五分钟回复数,
    sum(if(A.最晚回复时间>=A.最晚咨询时间 ,A.回复数,0)) 完整回复数
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st, et, own_type)
    df_g = df.groupby(['公司', '城市', '服务中心', '门店', '门店销售', '房源id', '渠道']).agg({'咨询数': 'sum', '回复数': 'sum',
                                                                             '五分钟回复数': 'sum', '完整回复数': 'sum'})

    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
    # df_g['im咨询五分钟回复率'] = round(df_g['五分钟回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
    # df_g['im咨询完整回复率'] = round(df_g['完整回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)
    # df_g = df_g.reset_index()
    df_g = df_util.df_set_first_title(df_g, "日")
    return df_g


def im_house_day(st=None, et=None, own_type=(1, 2, 3), channel_ids: list = [3, 20, 25]):
    """
    房屋维度的im相关数据
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:
    channel_id
    """
    if isinstance(channel_ids, str) or isinstance(channel_ids, int):
        channel_ids = [channel_ids]
    channel_ids = [str(channel_id) for channel_id in channel_ids]
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st, et, own_type)
    # return df
    df = df[df.channel_id.isin(channel_ids)]
    # 咨询数  k:原名  v:修改之后的名
    df_g = df.groupby(['房屋ID']).agg({'咨询数': 'sum', '回复数': 'sum', '五分钟回复数': 'sum', '完整回复数': 'sum'})

    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数', is_int=False), axis=1)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数', is_int=False), axis=1)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数', is_int=False), axis=1)
    # df_g = df_util.df_reset_index(df_g)

    return df_g


def im_lodge_week(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    A.work_name 门店,
    A.realname 门店销售,
    concat(A.lodge_id,"") 房源id,
    A.short_name 城市,
    A.name 渠道,
    A.咨询数,
    A.回复数,
    sum(if(timestampdiff(second, A.最早回复时间, A.最早咨询时间) <= 5*60,A.回复数,0)) 五分钟回复数,
    sum(if(A.最晚回复时间>=A.最晚咨询时间 ,A.回复数,0)) 完整回复数
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im(st, et, own_type)
    df_g = df.groupby(['公司', '城市', '服务中心', '门店', '门店销售', '房源id', '渠道']).agg({'咨询数': 'sum', '回复数': 'sum',
                                                                             '五分钟回复数': 'sum', '完整回复数': 'sum'})

    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
    # df_g['im咨询五分钟回复率'] = round(df_g['五分钟回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
    # df_g['im咨询完整回复率'] = round(df_g['完整回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)
    # df_g = df_g.reset_index()
    df_g = df_util.df_set_first_title(df_g, "周")
    return df_g


def im_lodge_month(st=None, et=None, own_type=(1, 2, 3)):
    """
    一月的 电话接起率
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = im(st, et, own_type)
    df_g = df.groupby(['公司', '城市', '服务中心', '门店', '门店销售', '房源id', '渠道']).agg({'咨询数': 'sum', '回复数': 'sum',
                                                                             '五分钟回复数': 'sum', '完整回复数': 'sum'})

    # df_g['im咨询回复率'] = round(df_g['回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询回复率'] = df_g.apply(lambda row: percentage(row, '回复数', '咨询数'), axis=1)
    # df_g['im咨询五分钟回复率'] = round(df_g['五分钟回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询五分钟回复率'] = df_g.apply(lambda row: percentage(row, '五分钟回复数', '咨询数'), axis=1)
    # df_g['im咨询完整回复率'] = round(df_g['完整回复数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询完整回复率'] = df_g.apply(lambda row: percentage(row, '完整回复数', '咨询数'), axis=1)
    # df_g = df_g.reset_index()
    df_g = df_util.df_set_first_title(df_g, "月")
    return df_g


def im_conversion_order_sale_day(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type)
    df_g = df.groupby(['公司', '城市', '服务中心', '门店销售', '渠道']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()

    return df_g


def im_conversion_order_sale_week(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type)
    df_g = df.groupby(['公司', '城市', '服务中心', '门店销售', '渠道']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()

    return df_g


def im_conversion_order_sale_month(st=None, et=None, own_type=(1, 2, 3)):
    """
    一月的 电话接起率
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type)
    df_g = df.groupby(['公司', '城市', '服务中心', '门店销售', '渠道']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()

    return df_g


def im_conversion_order_service_day(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type)
    df_g = df.groupby(['公司', '城市', '服务中心', '渠道']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()

    return df_g


def im_conversion_order_service_week(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type)
    df_g = df.groupby(['公司', '城市', '服务中心', '渠道']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()

    return df_g


def im_conversion_order_service_month(st=None, et=None, own_type=(1, 2, 3)):
    """
    一月的 电话接起率
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type)
    df_g = df.groupby(['公司', '城市', '服务中心', '渠道']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()

    return df_g


def im_conversion_order_city_day(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type)
    # return df

    df_g = df.groupby(['公司', '城市', '渠道']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()

    return df_g


def im_conversion_order_house_day_channel(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))

    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type, channel_ids)

    df_g = df.groupby(['房屋ID']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()

    return df_g


def im_conversion_order_online_sale_channel(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))

    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type, channel_ids)

    df_g = df.groupby(['门店销售']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()
    df_g = df_util.df_reset_index(df_g)

    return df_g

def im_conversion_order_offline_sale_channel(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))

    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type, channel_ids)

    df_g = df.groupby(['门店管家']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()
    df_g = df_util.df_reset_index(df_g)

    return df_g


def im_conversion_order_district_day_channel(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))

    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type, channel_ids)

    df_g = df.groupby(['大区']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()

    return df_g


def im_conversion_order_city_day_channel(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))

    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type, channel_ids)

    df_g = df.groupby(['公司', '城市']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()

    return df_g


def im_conversion_order_work_day_channel(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))

    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type, channel_ids)

    df_g = df.groupby(['城市', '门店']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()

    return df_g


def im_conversion_order_city_week(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type)
    df_g = df.groupby(['公司', '城市', '渠道']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()

    return df_g


def im_conversion_order_city_month(st=None, et=None, own_type=(1, 2, 3)):
    """
    一月的 电话接起率
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type)
    df_g = df.groupby(['公司', '城市', '渠道']).agg({'咨询数': 'sum', '下单数': 'sum'})

    # df_g['im咨询下单转化率'] = round(df_g['下单数'] / df_g['咨询数'] * 100, 2)
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    # df_g = df_g.unstack()

    return df_g


def im_conversion_order_lodge(st=None, et=None, own_type=(1, 2, 3), first_title='日'):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :param first_title:
    :return:

    """
    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type)
    df_g = df.groupby(['公司', '城市', '服务中心', '门店', '房源id', '门店销售', '渠道']).agg({'咨询数': 'sum', '下单数': 'sum'})

    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '咨询数'), axis=1)

    df_g = df_util.df_set_first_title(df_g, first_title)

    return df_g


def im_conversion_order_lodge_day(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday
    df_g = im_conversion_order_lodge(st=st, et=et, own_type=own_type, first_title='日')
    return df_g


def im_conversion_order_lodge_week(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :return:

    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday
    df_g = im_conversion_order_lodge(st=st, et=et, own_type=own_type, first_title='周')
    return df_g


def im_conversion_order_lodge_month(st=None, et=None, own_type=(1, 2, 3)):
    """
    一月的 电话接起率
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday
    df_g = im_conversion_order_lodge(st=st, et=et, own_type=own_type, first_title='月')
    return df_g


def im_conversion_order_house(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 im咨询回复率
    :param st:
    :param et:
    :param own_type:
    :param channels:
    :return:

    """
    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    df = im_conversion_order(st, et, own_type)
    df_g = df.groupby(["房屋ID"]).agg({'咨询数': 'sum', '下单数': 'sum'})
    df_g = df_util.df_rename(df_g, {"咨询数": "下单咨询数"})
    df_g['im咨询下单转化率'] = df_g.apply(lambda row: percentage(row, '下单数', '下单咨询数', is_int=False), axis=1)

    return df_g


def im_detail_day(st=None, et=None):
    """
    一周的 im咨询详情
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    return im_detail(st, et)


def im_detail_week(st=None, et=None):
    """
    一周的 im咨询详情
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    return im_detail(st, et)


def im_detail_month(st=None, et=None):
    """
    一月的 im咨询详情
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df_g = im_detail(st, et)
    df_g = df_util.df_set_index(df_g, '城市')
    return df_g


def im_time_detail_day(st=None, et=None):
    """
    一周的 im咨询详情
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    return im_time_detail(st, et)


def im_time_detail_week(st=None, et=None):
    """
    一周的 im咨询详情
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("im咨询回复率 st={st}".format(st=st))
    log.info("im咨询回复率 et={et}".format(et=et))
    return im_time_detail(st, et)


def im_time_detail_month(st=None, et=None):
    """
    一月的 im咨询详情
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("电话接起率 st={st}".format(st=st))
    log.info("电话接起率 et={et}".format(et=et))
    df_g = im_time_detail(st, et)
    return df_g


def online_refund_rate(st=None, et=None):
    """
    线上销售退款率
    :param st:
    :param et:
    :return:
    """
    order_people_online_df = order_people_online(st=st, et=et)
    refund_online_df = refund_online(st=st, et=et)
    refund_online_df = pd.merge(order_people_online_df, refund_online_df, how='left', on=['公司', '城市', '销售'])
    refund_online_df['退款率'] = refund_online_df.apply(lambda row: percentage(row, '退款人的数量', '下单人的数量'), axis=1)
    refund_online_df = df_util.df_set_index(refund_online_df, '城市')
    return refund_online_df


def offline_refund_rate(st=None, et=None):
    """
    线下管家退款率
    :param st:
    :param et:
    :return:
    """
    order_people_offline_df = order_people_offline(st=st, et=et)
    refund_offline_df = refund_offline(st=st, et=et)
    refund_offline_df = pd.merge(order_people_offline_df, refund_offline_df, how='left', on=['公司', '城市', '管家'])
    refund_offline_df['退款率'] = refund_offline_df.apply(lambda row: percentage(row, '退款人的数量', '下单人的数量'), axis=1)
    refund_offline_df = df_util.df_set_index(refund_offline_df, '城市')
    return refund_offline_df


def service_refund_rate(st=None, et=None):
    """
    线下管家退款率
    :param st:
    :param et:

    A.城市 ,
           group_concat(distinct A.服务中心) 服务中心,
    :return:
    """
    order_people_service_df = order_people_service(st=st, et=et)
    refund_service_df = refund_service(st=st, et=et)
    refund_service_df = pd.merge(order_people_service_df, refund_service_df, how='left', on=['公司', '城市', '服务中心'])
    refund_service_df['退款率'] = refund_service_df.apply(lambda row: percentage(row, '退款人的数量', '下单人的数量'), axis=1)
    refund_service_df = df_util.df_set_index(refund_service_df, '城市')
    return refund_service_df


def house_refund_rate(st=None, et=None, own_type=(1, 2, 3), have_long=True, is_int=False):
    """
    房屋退款率
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_int:
    :return:
    """
    order_people_house_df = order_people_house(st=st, et=et, own_type=own_type, have_long=have_long)
    refund_house_df = refund_house(st=st, et=et, own_type=own_type, have_long=have_long)
    refund_house_df = pd.merge(order_people_house_df, refund_house_df, how='left', on=['城市', '房屋ID'])
    refund_house_df['退款率'] = refund_house_df.apply(lambda row: percentage(row, '退款人的数量', '下单人的数量', is_int=is_int),
                                                   axis=1)
    # refund_house_df = df_util.df_set_index(refund_house_df, '城市')
    return refund_house_df[['房屋ID', '退款率']]


def work_refund_rate(st=None, et=None, own_type=(1, 2, 3), have_long=True, is_int=False):
    """
    线下管家退款率
    :param st:
    :param et:
    :param own_type:
    :param have_long:
    :param is_int:

    A.城市 ,
           group_concat(distinct A.服务中心) 服务中心,
    :return:
    """
    order_people_service_df = order_people_work(st=st, et=et, own_type=own_type, have_long=have_long)
    refund_work_df = refund_work(st=st, et=et, own_type=own_type, have_long=have_long)
    refund_work_df = pd.merge(order_people_service_df, refund_work_df, how='left', on=['城市', '门店'])
    refund_work_df['退款率'] = refund_work_df.apply(lambda row: percentage(row, '退款人的数量', '下单人的数量', is_int=is_int), axis=1)
    refund_work_df = df_util.df_set_index(refund_work_df, '城市')
    return refund_work_df


def city_refund_rate(st=None, et=None, own_type=(1, 2, 3), is_int=True):
    """
    城市退款率
    :param st:
    :param et:
    :param own_type:
    :param is_int:

    A.城市 ,
           group_concat(distinct A.服务中心) 服务中心,
    :return:
    """
    order_people_city_df = order_people_city(st=st, et=et, own_type=own_type)
    refund_city_df = refund_city(st=st, et=et, own_type=own_type)
    refund_city_df = pd.merge(order_people_city_df, refund_city_df, how='left', on=['城市', '公司'])
    refund_city_df['退款率'] = refund_city_df.apply(lambda row: percentage(row, '退款人的数量', '下单人的数量', is_int), axis=1)
    refund_city_df = df_util.df_set_index(refund_city_df, ['城市', '公司'])
    return refund_city_df


def district_refund_rate(st=None, et=None, own_type=(1, 2, 3), is_int=True):
    """
    城市退款率
    :param st:
    :param et:
    :param own_type:
    :param is_int:

    A.城市 ,
           group_concat(distinct A.服务中心) 服务中心,
    :return:
    """
    order_people_district_df = order_people_district(st=st, et=et, own_type=own_type)
    refund_district_df = refund_district(st=st, et=et, own_type=own_type)
    refund_district_df = pd.merge(order_people_district_df, refund_district_df, how='left', on=['大区'])
    refund_district_df['退款率'] = refund_district_df.apply(lambda row: percentage(row, '退款人的数量', '下单人的数量', is_int),
                                                         axis=1)
    refund_district_df = df_util.df_set_index(refund_district_df, ['大区'])
    return refund_district_df


def online_refund_rate_day(st=None, et=None):
    """
    日 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return online_refund_rate(st, et)


def online_refund_rate_week(st=None, et=None):
    """
    一周的 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return online_refund_rate(st, et)


def online_refund_rate_month(st=None, et=None):
    """
    一月的 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return online_refund_rate(st, et)


def offline_refund_rate_day(st=None, et=None):
    """
    日 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return offline_refund_rate(st, et)


def offline_refund_rate_week(st=None, et=None):
    """
    一周的 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return offline_refund_rate(st, et)


def offline_refund_rate_month(st=None, et=None):
    """
    一月的 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return offline_refund_rate(st, et)


def work_refund_rate_day(st=None, et=None):
    """
    日 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return work_refund_rate(st, et)


def work_refund_rate_week(st=None, et=None):
    """
    一周的 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return work_refund_rate(st, et)


def work_refund_rate_month(st=None, et=None):
    """
    一月的 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return work_refund_rate(st, et)


def service_refund_rate_day(st=None, et=None):
    """
    日 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return service_refund_rate(st, et)


def service_refund_rate_week(st=None, et=None):
    """
    一周的 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return service_refund_rate(st, et)


def service_refund_rate_month(st=None, et=None):
    """
    一月的 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return service_refund_rate(st, et)


def city_refund_rate_day(st=None, et=None, own_type=(1, 2, 3)):
    """
    日 退款率
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return city_refund_rate(st, et, own_type)


def city_refund_rate_week(st=None, et=None, own_type=(1, 2, 3)):
    """
    一周的 退款率
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return city_refund_rate(st, et, own_type)


def city_refund_rate_month(st=None, et=None, own_type=(1, 2, 3), is_int=True):
    """
    一月的 退款率
    :param st:
    :param et:
    :param own_type:
    :param is_int:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return city_refund_rate(st, et, own_type, is_int)


def house_refund_rate_day(st=None, et=None):
    """
    日 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return house_refund_rate(st, et)


def house_refund_rate_week(st=None, et=None):
    """
    一周的 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return house_refund_rate(st, et)


def house_refund_rate_month(st=None, et=None):
    """
    一月的 退款率
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("退款率 st={st}".format(st=st))
    log.info("退款率 et={et}".format(et=et))
    return house_refund_rate(st, et)


def refund_detail_day(st=None, et=None):
    """
    日 退款详情
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday

    log.info("退款详情 st={st}".format(st=st))
    log.info("退款详情 et={et}".format(et=et))
    return refund_detail(st, et)


def refund_detail_week(st=None, et=None):
    """
    一周的 退款详情
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday

    log.info("退款详情 st={st}".format(st=st))
    log.info("退款详情 et={et}".format(et=et))
    return refund_detail(st, et)


def refund_detail_month(st=None, et=None):
    """
    一月的 退款详情
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday

    log.info("退款详情 st={st}".format(st=st))
    log.info("退款详情 et={et}".format(et=et))
    return refund_detail(st, et)


def tujia_dis():
    con = db_util.mongodb_conn("tujia_account", "orders", db_util.mongodb_url_)
    df = pd.DataFrame(con.find({"yj_order_id": {"$exists": True}, "channelName": {"$exists": True}},
                               {"channelName": 1, "yj_order_id": 1, "_id": 0}))
    df = df.rename(columns={"channelName": "二次分销渠道", "yj_order_id": "订单ID"})
    df['订单ID'] = df.apply(lambda row: str(row['订单ID']), axis=1)
    return df


if __name__ == '__main__':
    pass
    a = phone_sale_month('2019-11-01', '2019-12-18')

    print(a)
    # x= [{'城市':'', 'im咨询回复率':'', 'im咨询五分钟回复率':'', 'im咨询完整回复率':''}]
    # b = refund_city('2019-10-18', '2019-10-18')
    # x = [{'公司': '', '城市': '', 'im咨询回复率': '', 'im咨询五分钟回复率': '', 'im咨询完整回复率': ''}]
    # df_g = pd.DataFrame(x)
    # a =  df_g[['公司', '城市', 'im咨询回复率', 'im咨询五分钟回复率', 'im咨询完整回复率']]
    # phone_sale_day
    # a.groupby(['公司', '城市' ]).agg({'im咨询回复率': 'sum' })
    # # b = pd.DataFrame(x)
    # print(a)
